PDA

View Full Version : [SOLVED] Sleeper: Control Range Scanned



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.

MWE
05-31-2005, 06:13 PM
I tried your code exactly as you provided it in Excell2000 and did not have the problem(s) you indicated. If there are no cells with conditional formats in the specified range, no cells are examined. If there are such cells, all are examined. It seems to do exactly what you want.

Am I missing something?