Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Apr 2004
    Location
    Albuquerque, New Mexico, USA
    Posts
    233
    Thanks
    0
    Thanked 1 Time in 1 Post

    Count color (2003 SP2)

    I want to count cells in a range of a particular color. I used this macro on a new sheet to find my color number, from http://www.mrexcel.com/ :
    <pre>Public Sub ColorTable()
    For i = 1 To 56
    Range("A" & i).Interior.ColorIndex = i
    Range("B" & i).Value = i
    Next i
    End Sub</pre>


    Then I used the code from <post#=28172>post 28172</post#> as amended in <post#=28180>post 28180</post#>. That gave me the correct color count number. But it does not automatically recalculate when I add or delete colored cells in my range, even with an F9. My Calculation option is set to Automatic. How do I get my count to update automatically?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Count color (2003 SP2)

    Add the line

    Application.Volatile

    immediately below the function header in the CountColors function.

  3. #3
    3 Star Lounger
    Join Date
    Apr 2004
    Location
    Albuquerque, New Mexico, USA
    Posts
    233
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Count color (2003 SP2)

    Hans, that works, <span style="background-color: #FFFF00; color: #000000; font-weight: bold">IF</span hi> I hit F9.

  4. #4
    3 Star Lounger
    Join Date
    Apr 2004
    Location
    Albuquerque, New Mexico, USA
    Posts
    233
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Count color (2003 SP2)

    How do I get my code to recalculate as soon as I add a colored cell in my designated range?

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Count color (2003 SP2)

    Changing a cells format does not trigger a recalculation event. Therefore, it is not possible to get the recalculate just on the change in cell color. WIth the:

    <code>
    Application.Volitile
    </code>

    the count will recalculate whenever any change is made that triggers a recalculate event (changing the value in any cell for example), or when you press F9. The only way to get closer to what you are asking would be to use the OnTime method to schedule a macro to run ever second that would do a recalculate on the sheet. However, this would add a lot of overhead to the sheet and could cause it to act very sluggish.
    Legare Coleman

  6. #6
    3 Star Lounger
    Join Date
    Apr 2004
    Location
    Albuquerque, New Mexico, USA
    Posts
    233
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Count color (2003 SP2)

    Thanks for the explanation. It appears that closing the spreadsheet and clicking Yes to save changes, then reopening the spreadsheet, does force a recalc without resorting to F9. I will explain to my users that they must use F9 if all they did was change color.

  7. #7
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Count color (2003 SP2)

    You can use the Worksheet_SelectionChange event to trigger a calculate. It's a lot of overhead for Excel, but it doesn't seem to slow it down too much in my sample workbook.
    Here is the event code which triggers a calculate after the user presses the enter key in the Area of Interest, which is the named range AOI.
    <pre>Option Explicit
    Private needCalc As Boolean

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If needCalc Then Application.Calculate
    needCalc = Not (Intersect(Target, Range("AOI")) Is Nothing)
    End Sub
    </pre>


    And here is the module code. Notice I added a SumIfColor function. Attached is my sample workbook. I hid column B since it does not apply to the user. Have fun. --Sam
    <pre>Option Explicit
    '
    Public Sub ColorTable()
    Dim i As Integer
    For i = 1 To 56
    Range("A" & i + 1).Interior.ColorIndex = i
    Range("B" & i + 1).Value = i
    Next i
    End Sub
    '
    Public Function CountIfColor(rng As Range, iColorIndex As Integer) As Long
    Dim c As Range, i As Long
    Application.Volatile
    i = 0
    For Each c In rng
    If c.Interior.ColorIndex = iColorIndex Then _
    i = i + 1
    Next c
    CountIfColor = i
    End Function
    '
    Public Function SumIfColor(rng As Range, iColorIndex As Integer) As Double
    Dim c As Range, acc As Double
    Application.Volatile
    acc = 0#
    For Each c In rng
    If c.Interior.ColorIndex = iColorIndex Then _
    acc = acc + c.Value
    Next c
    SumIfColor = acc
    End Function
    </pre>

    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  8. #8
    3 Star Lounger
    Join Date
    Apr 2004
    Location
    Albuquerque, New Mexico, USA
    Posts
    233
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Count color (2003 SP2)

    Sam,

    Thank you for this additional idea!

Posting Permissions

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