# Thread: Counting color-coded cells (Excel 2003)

1. ## Counting color-coded cells (Excel 2003)

I can't figure out what's wrong with this code!!! In Excel, I have a 12 x 31 grid that holds all the dates for 2008, each month on a different row. I need to mark specific days throughout the year in three different categories. I'm using cells J3 and L3 as a key. They are each filled with a different color. (The third category is therefore white.)

I need to add up how many cells in each row are each of the two colors -- the third category is then arrived at via a simple formula.
I can't see why this code isn't working (and pease, no rants about the lack of comments):
<pre>Sub CountDays()
Dim gDay As Long
Dim aTot As Integer
Dim gTot As Integer
Dim chMonth As Range
Dim chCell As Range
Dim i As Integer
'
gDay = Range("l3").Interior.ColorIndex
'
Set chMonth = Range("B4:AF4")
For i = 0 To 11
aTot = 0
gTot = 0
Set chMonth = chMonth.Offset(i, 0)
'
For Each chCell In chMonth
Select Case chCell.Interior.ColorIndex
aTot = aTot + 1
Case gDay
gTot = gTot + 1
End Select
Next chCell
'
Range("AG4").Offset(i, 0) = aTot
Range("AH4").Offset(i, 0) = gTot
Next i
End Sub</pre>

2. ## Re: Counting color-coded cells (Excel 2003)

You change chMonth *and* you change the row offset each time.
First time round, you process row 4.
Second time round, you procress row 4+1 = 5
Third time round, you process row 5+2 = 7
Fourth time round, you process row 7 + 3 = 10
etc.
You should change the line

Set chMonth = chMonth.Offset(i, 0)

to

Set chMonth = Range("B4:AF4").Offset(i, 0)

3. ## Re: Counting color-coded cells (Excel 2003)

I knew it would be something simple like that.

Set chMonth = chMonth.Offset(1, 0) -- but I just realized that it would always count one row ahead.

Thanks for the help. Sorry I'm such a dolt.

4. ## Re: Counting color-coded cells (Excel 2003)

You could have started with the row above the "start" row:

Set chMonth = Range("B3:AF3")

You could then have used

Set chMonth = chMonth.Offset(1, 0)

within the loop.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•