Listing Conditional Formatting Redux

Posted on by Dick Kusleika

Back in the day, I posted some code to list conditional formatting. It didn’t contemplate having multiple conditional formats for the same range. Because who would ever do that right? Of course that happens all the time and was very short-sighted of me. I aim to atone.

I used a Collection object because Collection objects can’t have two Keys that are the same. It’s a good way to get a unique list out of a list that contains duplicates. I used the range to which the FormatCondition applies as the key (and that was my downfall). My thought was this: I’m checking each cell individually and a FormatCondition that spans two cell would be counted twice. A FormatCondition that applied to L9:M9 would be counted for L9 and M9. By using the address as my unique key, it would only be counted once – the first time for L9 and it would error out and not be counted for M9.

Except you can have two FormatConditions that apply to L9:M9 and only the first would every be counted. I needed a way to identify what was a duplicate and what was a legitimate second FormatCondition. I cleverly devised (read stole from Bob Phillips) that I would add the count to the end of the address. But I got lucky in that it failed for my particular setup. The way my FormatConditions were created, they weren’t in the same order for all the cells. So even though an FC was the same for a later cell, it was the 3rd FC instead of the 2nd, and that made it seem unique.

I set out to find a better way to uniquely identify FCs, and here it is

It’s still no guarantee of uniqueness, but if you have two FCs with the same range, the same type, and the same formula, well, you gets what you deserves. Now I can use the ‘signature’ instead of the address.

And in case you forgot, here’s how I got the type.

Now this

gets you this

20 thoughts on “Listing Conditional Formatting Redux”

  1. Thanks for the update. I made a modification to list the CF for all worksheets.

    Sub ShowConditionalFormatting()

    Dim cf As Variant
    Dim rCell As Range
    Dim colFormats As Collection
    Dim i As Long
    Dim wb As Workbook
    Dim wsOutput As Worksheet
    Dim ws As Worksheet
    Dim aOutput() As Variant

    Set wb = ActiveWorkbook
    Set colFormats = New Collection
    Set wsOutput = Workbooks.Add.Worksheets(1)

    wsOutput.Range("A1:F1").Value = Array("Worksheet", "Type", "Range", "StopIfTrue", "Formula1", "Formula2")

    ' Populate of the Collection of format conditions
    For Each ws In wb.Worksheets
    On Error Resume Next
    For Each rCell In ws.Cells.SpecialCells(xlCellTypeAllFormatConditions).Cells
    For i = 1 To rCell.FormatConditions.Count
    With rCell.FormatConditions
    colFormats.Add .Item(i), CFSignature(.Item(i))
    End With
    Next i
    Next rCell
    On Error GoTo 0
    Next

    ' Transfer the Collection on the Output worksheet
    For i = 1 To colFormats.Count
    Set cf = colFormats(i)

    With wsOutput.Cells(i + 1, 1)
    .Value = cf.Parent.Parent.Name
    .Offset(0, 1) = FCTypeFromIndex(cf.Type)
    .Offset(0, 2).Value = cf.AppliesTo.Address
    .Offset(0, 3).Value = cf.StopIfTrue
    On Error Resume Next
    .Offset(0, 4).Value = "'" & cf.Formula1
    .Offset(0, 5).Value = "'" & cf.Formula2
    On Error GoTo 0
    End With
    Next i

    End Sub

    I often have the same rule, with the same AplliesTo adress across many worksheets, so I also had to modify the CFSignature function to

    Public Function CFSignature(ByRef cf As Variant) As String

    Dim aReturn(1 To 4) As String

    aReturn(1) = cf.Parent.Parent.Name ' Worksheet name
    aReturn(2) = cf.AppliesTo.Address
    aReturn(3) = FCTypeFromIndex(cf.Type)
    On Error Resume Next
    aReturn(4) = cf.Formula1

    CFSignature = Join(aReturn, vbNullString)

    End Function

  3. Jeff,

    When Excel crashes and I get the message “a number of invalid conditional formats were deleted” when Excel restarts and opens the recovered file. I might compare the recovered file with my last save instead of redoing the changes on my original file and maybe missing something.

    Also, I like to keep my workbook clean and consistent so that’s useful to have a list to scroll on one sheet instead of the Excel dialog.

    Seb

  5. @Dick: where have I heard that before?

    Oh yeah…

    We choose to go to the Conditional Formatting Rules Manager. We choose to go to the Conditional Formatting Rules Manager in this post and do the other things, not because they are easy, but because they are hard, because that goal will serve to organize and measure the best of our energies and skills, because that challenge is one that we are willing to accept, one we are unwilling to postpone, and one which we intend to win, and the others, too.”

  6. @Dick: I note the above doesn’t list formatting. Do you have a version that also spits out formatting?

    I’m building an app that lets users do a self assessment of their across a whole bunch of competency-based questions. I have some pretty complicated CF rules in the input sheet that progressively unhides batches of questions as they complete the current batch. I have multiple input sheets – one for each role. (Project Managers, Business Analysts, Project Coordinators) and when I want to set up some new input template for some new role, I just make a copy of one of the sheets, and change the name in the tab and the name at the top.

    But I find that making copies of the templates seems to move the order of the CF conditions around. Which is real problematic, because most of them have ‘Stop if true’ and if they aren’t in the right order, I don’t get the effect I’m after.

    (I also find that deleting or inserting rows screws up the order too. It’s weird.)

    I’m going to amend the above code to reset the CF rules to the ‘approved’ rules after a copy, and also to provide some kind of context for the rules, because using the existing CF dialog is like building a ship in a bottle.

  7. Eureka! Scrub that last question: I’ve found a quick and painless way to directly get complete details on ALL your CF in a workbook. Blog post coming in due course.

  8. Pingback: Daily Dose of Excel » Blog Archive » Easy way to back up CF formats

  9. One thing I don’t see is the Operator Property which is valid for “CellIs” & would add more info on the Condition selected.

  10. I’ve made some changes to process Operator:

    &
    On Error Resume Next
    .Offset(0, 4).Value = FCTypeOfOperator(cf.Operator)
    .Offset(0, 5).Value = “‘” & cf.Formula1
    .Offset(0, 6).Value = “‘” & cf.Formula2
    On Error GoTo 0
    Worksheet Type Range StopIfTrue Operator Formula1 Formula2
    CF Cell Value $A$2:$A$10 FALSE EqualTo =4
    CF Cell Value $A$2:$A$10 TRUE Between =1 =7
    CF Icon Sets $A$2:$A$10 FALSE

  11. Forgot this:
    Public Function FCTypeOfOperator(lIndex As Long) As String
    Select Case lIndex
    Case 1: FCTypeOfOperator = “Between”
    Case 2: FCTypeOfOperator = “Not Between”
    Case 3: FCTypeOfOperator = “EqualTo”
    Case 4: FCTypeOfOperator = “GreaterThan”
    Case 5: FCTypeOfOperator = “LessThan”
    Case 6: FCTypeOfOperator = “GreaterThanOrEqualTo”
    Case 7: FCTypeOfOperator = “LessThanOrEqualTo”
    Case Else: FCTypeOfOperator = lIndex
    End Select
    End Function

  12. I’ve been further expanding on the code above. In particular, I’ve been working on DATE tests & ICON SETS.
    I’ve had some good success, but developed a lot of other things about CFs that need research.
    In particuler:
    How ICON are stored
    &
    Why DATE CFs seem to create extremely long Formula
    PS the code I showed for Operator needed to check on cf.Type & I started using a Select to generate .Offset(0, 4).Value differently. I also made additional Functions.

  13. UPDATE:
    I still have more DATE Tests to do.
    The 1st Formula is created as a way of achieving the testing required for the DATE Testing & is also a part of other tests.
    I changed the name of the field I added before the 2 Formula.
    I’ve added more FCTypeOf functions & expanded the SELECT CASE for the field I added.
    For the time being, I’m still just doing further reseach on ICONSETs because it seem to be a bigger deal.
    I’ve had good sucess with BLANK, NO BLANKS, ERRORS & NO ERRORS & plan to work on DUPLICATE & UNIQUE.
    I may also make some effort on more formatting for the Workbooks printing & more.

  14. I’m still using my adaptation of the code, but I got a problem today that is worth sharing.

    I have a conditional format to highlight a whole column out of a set of columns. The associated range for the CF is L:QY, so 456 columns multiplied by 1,048,576 rows is a lot of cells to loop through.

    I was wondering why if was taking so long, and after crashing voluntarily Excel a few time before I had my Eureka moment!

  15. I once said that I couldn’t figure out how to iterate over FormatConditions. But why wouldn’t this work?

  17. Wow, I had somehow missed out on your post, Jeff. I assumed that if Dick couldn’t, I would likely not be able to figure it out as well. Thanks to both of you for your help.

    And two years later, Microsoft still hasn’t at least made the CF dialog resizeable!

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.