Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Lounger
    Join Date
    Jul 2002
    Location
    Sun Prairie, WI, USA
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Counting conditionally formatted cells (XL2003)

    I know counting formatted cells does not count those that are formatted via conditional formatting. There are plenty of examples to count normally formatted cells.
    I know Chip Pearson's (awesome!) site has a set of macros to count conditionally formatted cells if they meet specific criteria.
    However, my conditions are beyond the realm of his macros. Namely, they use the "Formula Is" method of conditionally formatting.

    Has anyone developed anything in any version of Excel to count conditionally formatted cells regardless of how the condition was applied?

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

    Re: Counting conditionally formatted cells (XL2003)

    I'd add extra columns with the formulas you use in conditional formatting. You can then count the number of TRUE values. The extra columns can be hidden if you wish.

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

    Re: Counting conditionally formatted cells (XL2003)

    John: If I understand what the original poster is looking for (it is not very clear), then I don't think this will do it either. Your code looks like it will count the number of cells in the current selection that have conditional formatting set. I think that what the poster wants is to count the number of cells that have been formatted in one of the formats specified in the conditional formatting. For example, the number of cells that have a red background because one of the conditions was met. Maybe the original poster can clearify what he/she wants.
    Legare Coleman

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Counting conditionally formatted cells (XL2003)

    If you are asking for more into about what Legare is suggesting then you might want to look at Conditional Formatting Colors on Chip Pearson's page. he has functions to look at what the active condition is and what the active formatting is.

    Steve

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

    Re: Counting conditionally formatted cells (XL2003)

    The OP mentions that Chip Pearson's functions don't apply: "However, my conditions are beyond the realm of his macros".

  6. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Counting conditionally formatted cells (XL2003)

    It's unclear what is being requested. I interpreted the request broadly:

    > Has anyone developed anything in any version of Excel to count conditionally formatted cells regardless of how the condition was applied?
    -John ... I float in liquid gardens
    UTC -7ąDS

  7. #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

    Re: Counting conditionally formatted cells (XL2003)

    Do the formulas use relative addressing? If so, you can't use a worksheet function because the formulas will not be evaluated properly, so you would have to use additional columns as Hans suggested, or use code to highlight the cells rather than CF. If not, then you should be able to use the methods on that site.
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    Lounger
    Join Date
    Jul 2002
    Location
    Sun Prairie, WI, USA
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting conditionally formatted cells (XL2003)

    Sorry for the confustion. Legare is on the right path regarding my original intent. I would like to know the number of cells that have been formatted due to conditional formatting.

    I've used Hans' suggested method of duplicating the CF in additional columns, but my current workbook has many, many CF'd columns. I was hoping to avoid this, but I think it is my only choice at this point.

    And yes, the CFs use relative addressing; more for convenience when copying them than anything else. Could be changed as needed but it would be tough to scale up that I'll eventually have to do.

    Haven't thought about using code to highlight the cells instead of using CF. This would allow for me to easily count the number of formatted cells after the fact.

    Thank you all for your input.

  9. #9
    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

    Re: Counting conditionally formatted cells (XL2003)

    If you don't need the counts to be dynamic, you could use a sub rather than a function. Because this can select all the cells and thereby evaluate the CF formulas properly, you would not need intermediate columns. You would need to remember to rerun the sub though (you could trigger it in a worksheet deactivate event, or Workbook_BeforeSave.)
    Regards,
    Rory

    Microsoft MVP - Excel

  10. #10
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Counting conditionally formatted cells (XL2003)

    <P ID="edit" class=small>(Edited by JohnBF on 31-Jul-07 07:18. )</P>Doesn't

    intCount = Selection.FormatConditions.Count

    work?

    Edit - no, it counts all the fomat conditions, which can be up to 3 per cell. I think there's code way back in this Forum, this code will count and highlight any conditionally formatted cell in the selected range:

    Sub CountCndtlFormatCells()
    ' Count cells with conditional format
    Dim rngCell As Range
    Dim intCndtlFormatCellCount As Integer

    Application.ScreenUpdating = False
    For Each rngCell In Application.Selection
    If CBool(rngCell.FormatConditions.Count) Then
    intCndtlFormatCellCount = intCndtlFormatCellCount + 1
    rngCell.Interior.ColorIndex = 38 ' highlight cells
    End If
    Next rngCell
    Application.ScreenUpdating = True
    MsgBox intCndtlFormatCellCount & " cells in Selection are conditionally formatted"
    End Sub
    -John ... I float in liquid gardens
    UTC -7ąDS

  11. #11
    Lounger
    Join Date
    Jul 2002
    Location
    Sun Prairie, WI, USA
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting conditionally formatted cells (XL2003

    You lost me. <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

    What Sub could I use to evaluate the CF formulas? The code up above does properly identify cells with CFs applied, but not whether the condition checked was true.
    Could you elaborate?

  12. #12
    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

    Re: Counting conditionally formatted cells (XL2003

    You would need to either convert Chip Pearson's function into a sub, or call it, passing ActiveCell as a parameter, from a sub that selects each cell in the range you are interested in. By activating the cells in question, the relative references should be evaluated correctly.
    HTH
    Regards,
    Rory

    Microsoft MVP - Excel

  13. #13
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Counting conditionally formatted cells (XL2003

    There are up to three conditions per CF - would you return TRUE if any are TRUE?

    (See also <post:=90,466>post 90,466</post:> for the complexity of CFs.)
    -John ... I float in liquid gardens
    UTC -7ąDS

  14. #14
    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

    Re: Counting conditionally formatted cells (XL2003

    Here's an example of what I mean.
    Regards,
    Rory

    Microsoft MVP - Excel

  15. #15
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Counting conditionally formatted cells (XL2003)

    [oops} I missed that comment.

    I do find this a little hard to believe though that they could not be adapted to his situation. The techniques used by Pearson are generic.

    Steve

Page 1 of 2 12 LastLast

Posting Permissions

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