Results 1 to 9 of 9
  1. #1
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Counting colours from conditional formatting

    Hi All,

    I've tried a variety of options to count colours that worked when the colour is not generated by conditional formatting, but can't get it to work when conditional formatting is applied.

    In the attached example you can see where I'm trying to do the count (starting at cell O9 & P9 and down)

    Any suggestions would be appreciated.

    Regards
    Attached Files Attached Files

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    In O9:
    =SUM(COUNTIF(E9:N9,$E$3:$N$3))
    entered with Ctrl+Shift+Enter
    In P9:
    =COUNT(E9:N9)-O9
    and fill down.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. The Following User Says Thank You to rory For This Useful Post:

    verada (2015-12-10)

  4. #3
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Thanks Rory,

    That works

    Just as a matter of interest, is there a way to count colours for conditionally formatted cells?

    Regards

  5. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 649 Times in 592 Posts
    Verada,

    You can use a line like this

    For Each cell In rng
    If Evaluate(cell.FormatConditions(1).Formula1) = True Then

  6. #5
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Thanks Maudibe,

    I'm still trying to learn VBA - how would this look as code?

    Regards

  7. #6
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 649 Times in 592 Posts
    Maybe something like this (not tested)

    Code:
    Dim rng As Range, cell As Range
    For I = 9 To 26
    Set rng = Range(Cells(I, "E"), Cells(I, "N"))
        For Each cell In rng
            If Evaluate(cell.FormatConditions(1).Formula1) = True Then
                Count = Count + 1
            End If
        Next cell
        Cells(I, "P") = Count
        Cells(I, "O") = 10 - Count
        Count = 0
    Next I
    For example, If condition 1 turns the cell green, you can count then number of incidents of True for that condition in a row which should be the same as the number of conditionally green colored cells.

    HTH,
    Maud
    Last edited by Maudibe; 2015-12-11 at 06:54.

  8. The Following User Says Thank You to Maudibe For This Useful Post:

    verada (2015-12-11)

  9. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    For basic colour CF it can be done but is not that simple - see Chip's page here: http://www.cpearson.com/Excel/CFColors.htm

    If you have 2010 or later there is a much better option - the DisplayFormat property - but you can't call it from a function in a cell - you'd have to use a subroutine eg an event.
    Regards,
    Rory

    Microsoft MVP - Excel

  10. The Following User Says Thank You to rory For This Useful Post:

    verada (2015-12-11)

  11. #8
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 649 Times in 592 Posts
    An alternative array formula that would also work in cell O9 is:

    {=COUNT(MATCH(E9:N9,$E$3:$N$3,0))}

    then copy down.

  12. The Following User Says Thank You to Maudibe For This Useful Post:

    verada (2015-12-16)

  13. #9
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Thanks all for your assistance once again - much appreciated

Tags for this Thread

Posting Permissions

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