Conditional Formatting.. The bane of my Excel Existence

[Jtucker10278]

Not even sure where to start with this.

The number in H3 is the Sum of C3+C4
The Values In M3, M4, M5 are the value I want conditional formatting to start with the color in that cell
so below 170 no format 170 to 194 Green, 195 to 224 Orange and 225 and up Red.
(the colors are there for visual aid I know that has nothing to do with how conditional formatting works)

=$H$3>$M$5 Red Applies to =$C$3:$C$5
=$H$3>$M$4 Orange Applies to =$C$3:$C$5
=$H$3>$M$3 Green Applies to =$C$3:$C$5

And everything is working just like it should.
How can I advance that set of circumstances/criteria to the next set of columns
instead of C, H, and M, I want to apply that same conditional formatting to D, using I, and N

everything else would be the same
=$D$3>$M$5 Red Applies to =$D$3:$D$5 etc.

and then next i want to advance down to the next set of rows (in other words I want it to operate the same way other excel formulas work)
is the only way to do this spending all night entering what will be almost 450 conditional formatting formulas for what I need to do

I tried removing the $ from the formula =H3>M5 because then the formula should "slide" in normal excel
but all that did was remove the conditional format from C4 and C5???
and that makes no sense to me I didn't touch the "Applies to" =$C$3:$C$5

And if there is any website or book that truly explains Conditional formatting please point me in that direction.
I have 5 phonebook size Excel books and have struggled with conditional formatting for the past 20 years and all i really seem to be able to find are lots of examples of different things conditional formatting can do. and an explanation of what the formula does. but vey little on how those formulas work

Seriously
I'm going to need someone to explain to me how
=$H$3> $M$3 Format Green Applies to =$C$3:$C$5 means if H3>M3 format cell C3:C5
but
=H3>M3 Format Green Applies to =$C$3:$C$5 somehow means if H3>M3 format cell C3.


Sorry for the frustration, Thanks for any help

 

[Burrgogi]

Someone just asked a question about conditional formatting yesterday - you might want to read this thread.

And if there is any website or book that truly explains Conditional formatting please point me in that direction.

Excel Conditional Formatting tutorial with examples

End-to-end tutorial will teach you how to use Conditional Formatting in Excel: apply preset rules and create new ones, copy existing formatting to other data, do conditional formatting based on another cell, and more.

www.ablebits.com

 

[RoryA]

I tried removing the $ from the formula =H3>M5 because then the formula should "slide" in normal excel

The references work the same. In your setup you want the rows locked, but not the columns, so you need:

=H$3> M$3

for example.