1

I would like to count the colors for each day (column) in selection. How can I loop through every cell in the selected columns? I've already implemented the color check code.

Example:

Ouput:

  • Day 15 has 8 purple and 3 green
  • Day 16 has 8 purple and 1 green

and so on..

enter image description here

Engo
  • 899
  • 3
  • 19
  • 49
  • can you show your code also, as there are a couple of ways of approaching it. – Nathan_Sav Jun 21 '17 at 08:02
  • If it is not conditionally formatted then you do not need VBA for this :). You may want to try [This](https://stackoverflow.com/questions/20489472/how-to-count-cells-in-a-range-with-a-value-less-than-another-cell-in-excel/20491479#20491479) – Siddharth Rout Jun 21 '17 at 08:09
  • If you still want to use Loops then see [This](https://learn.microsoft.com/en-us/dotnet/visual-basic/programming-guide/language-features/control-flow/loop-structures) – Siddharth Rout Jun 21 '17 at 08:13

4 Answers4

3
Sub test()
    Dim purpleCounter As Long, greenCounter As Long
    Dim c As Range, r As Range

    ' For each column in the selected area
    For Each c In Selection.Columns
        ' Reset the color counters
        purpleCounter = 0
        greenCounter = 0

        ' For each row (=cell) in the column we're working on cf. the previous loop
        For Each r In c.Rows
            ' If color of the cell matches our value, increment the respective counter
            If r.Interior.Color = 10498160 Then purpleCounter = purpleCounter + 1
            If r.Interior.Color = 7658646 Then greenCounter = greenCounter + 1
        Next r

        ' Print the results to the Immediate window - or replace this section with whatever
        Debug.Print "Day " & c.Rows.Item(1).Value & " has " & purpleCounter & _
            " purple and " & greenCounter & " green."
    Next c
End Sub

enter image description here

Vegard
  • 3,587
  • 2
  • 22
  • 40
  • You should **1.** Use `Dim c As Range, r As Range` at the beginning, enabling `Option Explicit` would have reminded you that you missed this. **2.** There is no reason to use `Integer` instead of `Long` [ [link](https://stackoverflow.com/a/26409520/3978545) ] – Wolfie Jun 21 '17 at 09:02
  • Valid points :) The code wasn't meant to be "production-grade" so much as a rudimentary solution to OP's challenge, so I hope my laziness isn't too much of a detriment. – Vegard Jun 21 '17 at 09:07
  • I upvoted because it was the neatest soln. here, just saying for the OP's benefit as much as yours :) – Wolfie Jun 21 '17 at 09:22
  • Thank you, this answers my question! – Engo Jun 21 '17 at 09:47
1

You can also use findformat looped on the complete column, to create a counter, doing the colour checking and the counting.

for example, something along these lines.

With Application.FindFormat.Interior
     .color=vbRed
end with

set r=range("c3:c30").find(what:="*", searchformat:=True)

Then loop until r is nothing, incrementing a counter. When looping r=range("c3:c30").find(what:="*", after:=r, searchformat:=True)

Thanks.

Nathan_Sav
  • 8,466
  • 2
  • 13
  • 20
0

For Next loop

For c = 15 to 17 'check the column num
    For r = 3 to 30 'assuming your last row is 30
       'color check code here
    next r
next c
kulapo
  • 397
  • 3
  • 15
-3
Dim Cell as Range
For Each Cell in Selection 
    If cell.row = ? Then ' enter code here
    If cell.Column = ? Then ? = Cell.Interior.Color
Next
Vegard
  • 3,587
  • 2
  • 22
  • 40
Benjamin Houot
  • 1
  • 3
  • 3
    please also add some text to your answer - not just a code block – Alexander Jun 21 '17 at 08:23
  • It is the first time I answer a question, i try to format my code. but can't find find how to make it pretty... – Benjamin Houot Jun 21 '17 at 08:27
  • just indent the code with 4 spaces and separate it from the text-only with one empty line. No tags needed – Alexander Jun 21 '17 at 08:35
  • 1
    Even with the [code formatting improvements](https://meta.stackexchange.com/questions/22186/how-do-i-format-my-code-blocks), you are missing 2 things. **1.** comments to go with your code, please elaborate on what the various lines do, what your code does overall, any references to source material which might also help. **2.** the code isn't valid VBA because of the way you have structured the blank spaces. Consider using actual variables instead of question marks, so the code may need *tweaking* but the reader doesn't have to think "What do all these invalid characters mean?" – Wolfie Jun 21 '17 at 08:48
  • Ok thank you for the answers, i will imrove my future answers :) – Benjamin Houot Jun 21 '17 at 09:22
  • @Benjamin Houot This post could be edited to provide a correct answer. I recommend that you begin by removing the `?` and `' enter code here`, and then think about how you would turn the code into a method that returns a string of the form `"Day " + DayNumber + " has " + PurpleCount + " purple and " + GreenCount + " green"` – WonderWorker Jun 22 '17 at 09:51