View Full Version : [SOLVED] coping conditional format and moving the compare cell with it

03-04-2019, 02:01 PM
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 :(


03-05-2019, 12:19 AM
Hi selfteaching!
Please refer to the Attachment.
You can copy or drop the E column cells.

03-05-2019, 07:30 AM
Use OFFSET() in the CF formula and you should be able to copy the first CF and paste to the rest


03-05-2019, 10:12 AM
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 :beerchug: :beerchug:

03-05-2019, 06:27 PM
Hi selfteaching!
I'm sorry, I may have misunderstood you.

03-07-2019, 02:13 PM
Not a problem. I'm glad you tried