Cyberdude
04-25-2005, 12:16 PM
I wrote a procedure to look at the formula in a conditional format and replace a string I specify with another string I specify. It works. My problem is that I can't seem to control which cells are scanned. Whether I specify one cell address or several, it seems to scan the whole page. What do I need to restrict the scan to just the cells of interest? This is the core code I'm using:
Sub Test()
Dim Cel As Range, MyRange As Range, OldStrg$, NewStrg$, Msg$
Dim OrigFormula$, NewFormula$, CondFmtsFoundCnt%, CondFmtsChangedCnt%
OldStrg = "Beep"
NewStrg = "Boop"
Set MyRange = Range("F1").SpecialCells(xlCellTypeAllFormatConditions)
For Each Cel In MyRange
OrigFormula = Cel.FormatConditions(1).Formula1
'Count each cond fmt found
CondFmtsFoundCnt = CondFmtsFoundCnt + 1
If InStr(1, OrigFormula, OldStrg) > 0 _
Then
'Replace current string as many times as it occurs (=-1)
NewFormula = Replace(OrigFormula, OldStrg, NewStrg, 1, -1)
Cel.FormatConditions(1).Modify xlExpression, xlEqual, NewFormula
'Count each cond fmt changed
CondFmtsChangedCnt = CondFmtsChangedCnt + 1
End If
Next
Msg = CondFmtsFoundCnt & " conditional formats examined." & vbCr & _
CondFmtsChangedCnt & " conditional formats changed"
MsgBox Msg
End Sub
I put conditional formats on 4 cells scattered around the sheet, and only one of them contained the string I wanted to change. The routine would pick it out and change it every time, no matter what range I specify.
Sub Test()
Dim Cel As Range, MyRange As Range, OldStrg$, NewStrg$, Msg$
Dim OrigFormula$, NewFormula$, CondFmtsFoundCnt%, CondFmtsChangedCnt%
OldStrg = "Beep"
NewStrg = "Boop"
Set MyRange = Range("F1").SpecialCells(xlCellTypeAllFormatConditions)
For Each Cel In MyRange
OrigFormula = Cel.FormatConditions(1).Formula1
'Count each cond fmt found
CondFmtsFoundCnt = CondFmtsFoundCnt + 1
If InStr(1, OrigFormula, OldStrg) > 0 _
Then
'Replace current string as many times as it occurs (=-1)
NewFormula = Replace(OrigFormula, OldStrg, NewStrg, 1, -1)
Cel.FormatConditions(1).Modify xlExpression, xlEqual, NewFormula
'Count each cond fmt changed
CondFmtsChangedCnt = CondFmtsChangedCnt + 1
End If
Next
Msg = CondFmtsFoundCnt & " conditional formats examined." & vbCr & _
CondFmtsChangedCnt & " conditional formats changed"
MsgBox Msg
End Sub
I put conditional formats on 4 cells scattered around the sheet, and only one of them contained the string I wanted to change. The routine would pick it out and change it every time, no matter what range I specify.