Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Oct 2004
    Location
    Indianapolis, Indiana, USA
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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 aDay As Long
    Dim gDay As Long
    Dim aTot As Integer
    Dim gTot As Integer
    Dim chMonth As Range
    Dim chCell As Range
    Dim i As Integer
    '
    aDay = Range("j3").Interior.ColorIndex
    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
    Case aDay
    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. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #3
    Lounger
    Join Date
    Oct 2004
    Location
    Indianapolis, Indiana, USA
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting color-coded cells (Excel 2003)

    I knew it would be something simple like that.

    I had originally had it as
    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. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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
  •