Conditional formatting fill down

Question

Hi I am having a little trouble trying to get condition formatting to fill down.

Here is my formula  =AND($M3<>"",$T$74<=$M3)

M3 refers to a value (high jump height)

T74 refers to the school record

I want to apply the formula and formatting into m3 and fill down into m4, m5 and so on, however when I try the drag method or format painter each cell still shows the reference $M3.  This could be in M12 or M100.  I thought not having an absolute "$" that the number should automatically update and the column remain fixed.

The reason I am not using a straight forward highlight if equal or greater than option is because in the m column if there is no data entered the cell gets highlighted and does not remain blank.  It does not create this issue if the format is equal to or less than, (this works fine for all the track athletics events, as you aim to be faster, but field events you want to jump higher/further etc.)

Any suggestions would be most appreciated.

Also as an extra question

How would you go about telling a cell that if it had the numbers 15-17 to be green, 18-19 to be blue and 20 to be red with the priority always going to whichever number is highest?  So the higher the number that conditional format would take precedence.

Many thanks

Marcus

Answer 1

Hi thanks for the reply.  I have tried copy and paste special and using format painter (the little brush).  what I have found is that if I am copying the conditional format from cell M1 to M2:M10 then cell M2 will automatically update its reference but cell M3 and below will still state $M2 as its reference.  Its odd how it fills down automatically just one row but then repeats for the next several rows.

Answer 2

If cell M3 is selected when you enter that formula into the CF condition, then filling the cell down should indeed move the CF formula to successive cells in column M, and the formula should update to M4, M5, etc. You can also try selecting M3, copying it and then selecting M4:Mwhatever and then paste.

You need to add three condition formulas, the first, >19, the second >17, and the third >14