Help with 3-level Conditional formatting + fill down

[maximillianrg]

Hello and thank you for the help

J3=215, J4=214 and so on to J93

N3=215 and conditional formatting for this cell has 3 rules
Cell Value < J3 (green format) applies to =$N$3:$N$5
Cell Value = J3 (yellow format) applies to =$N$3:$N$5
Cell Value > J3 (red format) applies to =$N$3:$N$5

The above works as designed and if cell N15 = greater that J3 its red, equal to J3 its yellow, and less than J3 its green - this is perfect

When I do a fill down if J3 into J4 and J5 the conditional formatting no longer works as I want N4 to look at J4 and apply the same conditional formatting with the goal to have this work when filled down to J93 - My guess is its the $ somewhere but I'm stuck

Max

 

[KolGuyXcel]

Combine the three conditions into 1 condition using nested IF formula!

like,

=IF(N3<J3, (green format), IF(N3 = J3, (yellow format) ...

But does it have to be a rowwise comparison? i.e. would N3 be compared to J3, N4 compared to J4 and so on? Not sure why is that required.

If that's not the case and N3:N5 needs to be compared only with J3 then in the formula use $J$3 instead of J3.

 

[maximillianrg]

While one could write an if statement like =if(N3<J3"G",if(N3=J3"Y","R")), I do not know how to use this in conditional formatting to shade a cell so I'm lost here. Basically I'm creating a weight loss journal and the person logs their weight daily. Column N represents the weight of the day and the weights prepopulated in column J is the target for the day. If their we in us inder target, the cell is green, if equal to its yellow and of over its red.