Copying/Extending Conditional Formatting - Relative Cell Reference versus Absolute

[RMac59]

Hi I apologize if this is a duplicate request. This is the first time I have used this forum. I thought I posted a request this morning but I do not see it. 

Here is my challenge.

I have conditionally formatted cell C5 that contains a formula as,

C5>C4 color red

C5<C4 color green

Now, I want to extend the rule on a relational basis to the remaining columns in the spreadsheet. For example. I would like to copy the rule in cell C5 to cell D5 including formatting. The end result I would like to see in D5 is,

D5>D4 color red

D5<D4 color green

This is not working for me for a few reasons,

I cannot remove the absolute reference from $C$5 in the rules. When I do change it, excel changes it back and when copied using format painter the applies to is now a range $C$5:$D$5.

Secondly, the rule itself remains as, 

C5>C4 color red

C5<C4 color green

I would like the condition to behave in the same manner as copying a formula from C5 to D5.

In cell C5 for example is C4-C3. If copied to D5, it would be D4-D3.

I can achieve what I want by manually formatting each cell but that would be a long a tedious process.

I hope I have explained my challenge clearly. I appreciate any assistance. Thank you.

[#2]

@RMac59 

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.

[#3]

@mathetes 

https://1drv.ms/u/s!Akyo43cv1NG3pE1E51Y3CMcNWwYL?e=6xgUQf 

Hopefully this helps make my description more clear.

Thanks!

[#4]

The '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

[#5]

Thanks so much! I got it to work for me. I really appreciate the help!!!