coping conditional format and moving the compare cell with it

[selfteaching]

Hi
I’m trying to have conditional format change the format of a cell to red if a condition is true based on a different cell and then copy it down about 30 cells with the based cell moving with it

For ease of explaining:
When I enter a number in E4, I want it to look at I3.
If it’s not the same, change the font in E4 to red, so I will notice it.
Then later when I enter a number in E5, I want it to look at I4, with the same condition.
Then E6 compares with I5, etc
When copy it down, it still looks at I3
I tried to paste>>special>>all merging conditional format
I also tried to use the format painter from the ribbon
The conditional format formula adds the &’s ($I$3)
When I change it to I3, it doesn’t work

I can't make it work because the compare cell will change
I don't really want to make a different conditional format rule in each E cell 30 times

mike

[大灰狼1976]

Hi selfteaching!
Please refer to the Attachment.
You can copy or drop the E column cells.

[Paul_Hossler]

Use OFFSET() in the CF formula and you should be able to copy the first CF and paste to the rest

Capture.JPG

[selfteaching]

Thank you Paul_Hossler
it worked like a charm

Hi 大灰狼1976,
I tried your formula, but I must of done something wrong. It formatted all the cellls red after I copied the formatting down and the reference cell stayed the same.

Thanks for your help, though


mike

[大灰狼1976]

Hi selfteaching!
I'm sorry, I may have misunderstood you.

[selfteaching]

Not a problem. I'm glad you tried

mike