2

I have a problem with a simple conditional format rule in Google Sheets.

I want to color the cells in range M5:M90 whose values are greater than or equal to the value in cell M2.

I thought it was pretty straightforward however it is highlighting many values in the range that do not meet the criteria.

What can I do to fix this?

My Rule:

Apply to range
M5:M90

Format cells if...
Greater than or equal to
=M2

Current rule dialog box

Link to my Google spreadsheet

Screenshot of my Google spreadsheet

2 Answers 2

0

You were very close!

The correction needed is to make the row reference absolute in the rule's cell reference:=M$2 instead of =M2.

The table below compares how an absolute row reference gets applied by the rule versus a relative one:

=M$2   =M2                                                                                          
Row 5 =M$2 =M2
Row 6 =M$2 =M3
Row 7 =M$2 =M4
   .
   .   
   .
   .
   .   
   .
   .
   .   
   .
Row 88 =M$2 =M85
Row 89 =M$2 =M86
Row 99  =M$2 =M87
1
  • 1
    Thank you so much it worked!
    – hubertwah
    Commented Jul 12 at 0:42
0

Open the Conditional format dialog with the top cell in your range selected. Modify the "Apply to Range" from F5 to F5:F13 (or whatever your range is). In Format Rules, select Custom formula, then write your formula below in a format like this: =$F5>25.

Make sure that your $ is in front of the column if you want to apply this to a vertical range.

I recently used "Apply to Range" A6:G250 with a formula =$D6="Payment", so every row that said "Payment" in column D was successfully conditionally formatted.

Think through your formula as though it is for one cell. Lock the row or column that you wish the criteria to be bound to, then apply the format to the range in "Apply to Range."

1
  • Thank you! Will try that!
    – hubertwah
    Commented Jul 12 at 0:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.