Forum Discussion
Copying/Extending Conditional Formatting - Relative Cell Reference versus Absolute
I use Conditional Formatting quite frequently, and almost always find I have to tweak multiple times to get the result I need; in other words, it's NOT intuitive, so requires patience.
Reading your description, my sense is that you don't what to totally remove the absolute reference from $C$5 (and, I presume $C$4). Instead of going all the way from $C$5 to C5, go to C$5. That format rule, painted over to column D would become D$5.
But my main point is don't give up.....if you could post an image of the actual Conditional Formatting rule dialog box, I or somebody else might be able to give more ;pointed and specific guidance.
- RMac59Sep 22, 2022Copper Contributor
https://1drv.ms/u/s!Akyo43cv1NG3pE1E51Y3CMcNWwYL?e=6xgUQf
Hopefully this helps make my description more clear.
Thanks!
- mtarlerSep 22, 2022Silver ContributorThe 'Applies to' range will be absolute range references and that is fine. All that matters is the formula being used. So the formula is applied to the upper left corner cell of the 'Applies to' range. And THEN when it is 'applied to' every other cell in that range the formula is adjusted based on relative and absolute reference within that formula. So in your case you can change Applied to range to be $C$5:$F$100 and then with a formula like =C5>C4 it will apply that to cell C5 and then for C6 it will "apply" =C6>C5 and for D5 it will "apply" =D5>D4
alternatively if you always wanted to compare to the value in row 4 of the same column then you would use a formula like =C5>C$4 so that row 4 is fixed but the column is relative.
hope that helps- RMac59Sep 23, 2022Copper ContributorThanks so much! I got it to work for me. I really appreciate the help!!!