Google Sheets conditional formatting doesn't apply to the whole range

Question

I am trying to conditionally format a range of cells based on the value of a different cell. I have selected the range of cells, chosen "custom formula" and entered =reference cell="reference value". (see screenshot) this should make the whole range of cells purple if cell G4 equals the letter M. The problem is only the top-left cell in the range is changing color, not the whole range.

Also, I want to drag this block of cells down to repeat it, and for the conditional formatting to fill down appropriately (i.e. to reference cell G21 when I fill downwards and so on.) How do I do that?

Answer 1

you need to lock it down with $

try:

=$G$4="M"

or:

=G$4="M"