Find and Replace In Conditional Formats

Yesterday I made a comment on twitter that I was trying to figure out if it would be faster to check the conditional formatting formulas in 40 workbooks manually to ensure they were correct, vs writing a tool to do it.  My buddy Jon Peltier tweeted back saying “Don’t know about faster, but it’s got to be more fun to code it.”  I thought about it, but then when I found that one workbook returned a count of 250 rules… the answer was to code it.

Well, yes, Jon was right… if your definition of fun is akin to performing home dentistry for your root canal.

The code I used to modify my conditional formats has been incredibly unreliable, and is returning different effects depending on the users’ machine that it is run on, the version of Excel, and maybe a few other things.  (It did different things after I had lunch too!)

I finally gave up on re-writing the conditional formatting rules, and ended up just running a macro to tell me which formulas I needed to edit:

  1. Sub FindCFIssues()
  3.     Dim ws As Worksheet
  4.     Dim ftc As FormatCondition
  5.     Dim sFormula As String
  6.     Dim sFind As String
  7.     Dim sReplace As String
  8.     Dim lCount As Long
  10.     On Error Resume Next
  11.     sFind = "ops-Internal"
  12.     sReplace = "ops_Internal"
  14.     For Each ws In ActiveWorkbook.Worksheets
  15.         For Each ftc In ws.UsedRange.FormatConditions
  16.             With ftc
  17.                 sFormula = Replace(.Formula1, sFind, sReplace, 1, compare:=vbTextCompare)
  18.                 If .Formula1 <> sFormula Then
  19.                     Debug.Print ws.Name & ": " & .AppliesTo.Address
  20.                     lCount = lCount + 1
  21.                 End If
  22.             End With
  23.         Next ftc
  24.     Next ws
  26.     If lCount > 0 Then
  27.         MsgBox "All done, a few issues were found", vbCritical
  28.     Else
  29.         MsgBox "No issues!", vbInformation
  30.     End If
  31. End Sub

Ultimately, this code searches for “ops-internal”, and flags my attention, because it should be “ops_Internal”.

Some weirdness here too… the “Next ftc” portion of the code runs until it’s gone through all the rules, then errors.  I had to add the On Error Resume Next above in order to let it continue, as it choked with just “Next” inside the other loop.

So at least I’ve got something now that quickly tells me if I have an issue, and prints out the offending rules in the VBA Immediate window.  I’d rather have the replacement done, but really don’t have the time to work through all the idiosyncrasies… err.. I mean fun… that will be involved in getting it right.

3 thoughts on “Find and Replace In Conditional Formats

  1. Hey David,

    Agreed. My original hope was to also make the find and replace work, but with the instability I experienced, (the formula I placed wasn’t what Excel ended up applying) it killed it for me (at least for the short term.)

    Giving a UI to this part shouldn’t be too tough though. :)

  2. Pingback: The Ken Puls (Excelguru) Blog » Blog Archive » Conditional Formatting Formula Inspector

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>