Conditional Formatting Code

[Shawn]

I recorded this code but I need it to do more and work a little differently.
I need it to first remove any existing "Conditional" formatting. Not all
formatting, just remove the existing conditional formatting. I then need to
add the conditional formatting as detailed below.

Also, my intent was to copy the formatting from the first cell into others.
I need it to do all this with out selecting (.Select). Please help.

Sheets("Travel").Range("R7:R8").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=(IF(R9>0,0,IF(SUM(T9)>0,1,0)))>0"

Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = True
Selection.Copy
Range("R9:R10,R12:R13,R15:R16").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

 

[Bob Bridges]

Doing it without Select is easy; wherever it says "<something
something>.Select" change it to "Set <variable name> to <something
something>", and then wherever it says "Selection.<anything>" change it to
"<variable name>.<anything>". So, for example, the first two lines of your
recording can be changed to

Set oRng = Sheets("Travel").Range("R7:R8")
oRng.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND(R9<=0,SUM(T9)>0)"

(You can use your IF functions if you want, but I think mine is simpler.)
oRng, in my example, is simply a variable name I picked to hold the specified
range, or any other object for that matter; you can pick another name if you
prefer.

To do this to a larger area you can set a larger range, or you can set up a
loop to do it in different areas throughout your sheet. I'm not sure of the
details.

Feel free to ask more questions; the above is pretty sketchy, on the
assumption that you know the basics and just need a hint or two.