Mass Change Of Cond'l Fmting Variable Names

[Cyberdude]

I do a LOT of conditional formatting. I have the need to change a defined name that is used a LOT in my conditional format condition statements. I can guess the answer, but the question is: Does Excel have something like "Find-and-Replace" that works on conditional formats? :bug:

[Jacob Hilderbrand]

If you change what the Named Range refers to then the Conditional Formatts would automatically refer to the new range.

You can use VBA to check the Conditional Format and change it to something else.



Option Explicit

Sub Macro1()
Dim Cel As Range
Dim MyRange As Range
Dim StrFormula As String
Set MyRange = ...
Set MyRange = MyRange.SpecialCells(xlCellTypeAllFormatConditions)
For Each Cel In MyRange
StrFormula = Cel.FormatConditions(1).Formula1
If InStr(1, StrFormula, "String 2") > 0 Then
'Do Something
End If
Next
End Sub


If you want to deal with all the Conditional Formats that are 100% the same you can use this:



SpecialCells(xlCellTypeSameFormatConditions)

[Cyberdude]

Yeah, DRJ, just what I was looking for. Well, I'd hoped for an Excel solution, but I can work with the VBA solution. Just grrrreat! :friends:

[Jacob Hilderbrand]

Glad to help. :beerchug:

Take Care