title: Google Sheets conditional formatting doesn't apply to the whole range
body: <p>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.  </p>
  
  <p>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?</p>
  
  <p><img src="https://i.stack.imgur.com/8Ah0g.jpg" alt="enter image description here"></p>