Copy conditional formatting with referance cells

[pete4monc]

Hi all

I have a problem which I have been trying to solve for two days now. I've even started to write a macro to try to solve it, with no luck (I'm new to vba).
I don't know if this is the right section to post this, about conditional formatting referancing certain cells and then copying it down to other cells in the same column.
Todays date in C1.
Starting date in G4
Completion date in H4
Alert days in H1 (ie number of days "2" to trigger the conditional format and that will turn H4 cell red.

My formula in conditional formatting is (and it works in the first cell H4)
=($C$1-G4)>$H$1

I would like to copy the conditional formatting down from H4 to approx H1000 but when I "format painter" the conditional format will only copy correctly to the first cell ie H5 and then after is will say
=($C$1-G5)>$H$1 throughout all the cells I copy it to??


I hope I have explained it well enough so all can understand and someone could help?

Mega thanks
Pete

 

[Joe4]

I would like to copy the conditional formatting down from H4 to approx H1000 but when I "format painter" the conditional format will only copy correctly to the first cell ie H5 and then after is will say
=($C$1-G5)>$H$1 throughout all the cells I copy it to??

Don't be fooled! Excel is probably doing it correctly, even though it looks a little funny.
When you apply a Conditional Formatting rule to a whole range at once, either my selecting the whole range first and entering the rule, or whether using Format Painter on a whole range, when you view the rule, it will show the Conditional Formatting formula as it applies to the very first cell in that range. So, if you have use absolute/relative/mixed cell referecing correctly (which it looks like you have), it should work properly.

What I usually do when I want to apply Conditional Formatting to a whole range of cells is to first select all the cells that I want to apply it to, then I write the formula as it applies to the very first cell in that selected range. As long as you have written the formula with the correct range reference types, Excel will adjust it correctly for all the others cells (even if it doesn't look that way when you view the rule).

 

[pete4monc]

Joe4 - thanks for that. I have sorted the copy and paste now.

It was just confusing that the formula was showing something different so I thought it was looking at the wrong cell!!

Thanks for your help in clearing that up.

 

[Joe4]

It was just confusing that the formula was showing something different so I thought it was looking at the wrong cell!!

Yes, it definitely can be a little confusing/misleading.
The thing to remember is that the formula shown reflects the formula for the first cell in the range you applied it to.