stopher2475
asked on
Conditional Formatting not copying properly.
I have a conditional format on a cell that I would like to copy down the whole sheet.
The problem is when I copy it down using the format painter, all the cells will reference the first cell in the range I'm formatting so in this case they are all looking at G4 instead of their respective rows.
I could do this with a loop but I'd rather not if possible. It takes too long to run.
Any idea?
Range("G3").FormatConditions.Add Type:=xlExpression, Formula1:="=IF(G3=""PASSED"",TRUE,FALSE)"
Range("G3").FormatConditions(Range("G3").FormatConditions.Count).SetFirstPriority
Range("G3").FormatConditions(1).Font.Color = -11489280
Range("G3").FormatConditions(1).Font.TintAndShade = 0
Range("G3").FormatConditions(1).StopIfTrue = False
The problem is when I copy it down using the format painter, all the cells will reference the first cell in the range I'm formatting so in this case they are all looking at G4 instead of their respective rows.
I could do this with a loop but I'd rather not if possible. It takes too long to run.
Any idea?
Try using r1c1 (relative addressing) in your .Add method.
Formula1:="=IF(RC=""PASSED"",TRUE,FALSE)"
ASKER
I actually want to leave it as G3 becuase I'm going to format the 2 cells to the left of it based on that cells value. Will try the RC notation. I think aikimark is onto something.
Attacking the problem from the other direction, what code are you using for the format painter?
I just recorded a macro and it gave me this (and seemed to work):
Also, if you're going to use the same formula on another column you probably want a $ infront of your G to make it $G3.
I just recorded a macro and it gave me this (and seemed to work):
Selection.Copy
Range("C7:C8").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Also, if you're going to use the same formula on another column you probably want a $ infront of your G to make it $G3.
ASKER
Tried the RC notation for the range.
=IF(G3="PASSED",TRUE,FALSE )
How can I get it to reference the rows validation cell?
Range("G3:G2310").FormatConditions.Add Type:=xlExpression, Formula1:="=IF(RC=""PASSED"",TRUE,FALSE)"
Range("G3:G2310").FormatConditions(Range("G3:G2310").FormatConditions.Count).SetFirstPriority
Range("G3:G2310").FormatConditions(1).Font.Color = -11489280
Range("G3:G2310").FormatConditions(1).Font.TintAndShade = 0
Range("G3:G2310").FormatConditions(1).StopIfTrue = False]
The expression in all of them is referencing the first cell of the range.=IF(G3="PASSED",TRUE,FALSE
How can I get it to reference the rows validation cell?
Try:
Range("G3").FormatConditions.Add Type:=xlExpression, Formula1:="=IF(G3=""PASSED"",TRUE,FALSE)"
Range("G3").FormatConditions(Range("G3").FormatConditions.Count).SetFirstPriority
Range("G3").FormatConditions(1).Font.Color = -11489280
Range("G3").FormatConditions(1).Font.TintAndShade = 0
Range("G3").FormatConditions(1).StopIfTrue = False
Range("G3").Copy
Range("G4:G20").Select
Range("G4").PasteSpecial xlPasteAllMergingConditionalFormats
ASKER
That only does the one cell G4. I change line 8 to:
Range("G4:G20").PasteSpeci al xlPasteAllMergingCondition alFormats
but they all reference G4 for the check.
I think I may be stuck with using a loop. Sucks because it's a lot slower.
Range("G4:G20").PasteSpeci
but they all reference G4 for the check.
I think I may be stuck with using a loop. Sucks because it's a lot slower.
stopher, I think you missed my last comment.
Produced this paste formats code with record macro and this worked for me on a small scale test.
Selection.Copy
Range("C7:C8").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Produced this paste formats code with record macro and this worked for me on a small scale test.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
What's weird is that works but when I check the format it appears to be referencing the first Cell "G4". It was probably working before but when I saw the reference to the first cell I had assumed it wasn't. I guess I don't understand the notation fully.
In the conditional formatting manager the formula shown is always that for the first cell in the range it's applied to. We probably should have realised that was what you were looking at :/
ASKER
Good to know. Thanks for the help!
Open in new window
(I think your formula isn't working because the G3 is hardcoded into the formula and apparently the format painter isn't changing it on paste - this dodges that issue)