It drives me crazy that I don't know how to limit the range of a conditional formatting rule to the last row in a table I've created.

The Rule

The rule I have created is supposed to turn all blank cells in one row of the column red, and turn those same cells yellow as soon as they have content. That aspect works perfectly.


Condition: Cell contains a blank value

Applies to: =$C$2:$C$1469

Format: Red Fill (255,124,128)


Condition: Cell does not contain a blank value

Applies to:=$C$2:$C$1469

Format: Yellow Fill (255,255,0)

The Problem

However, I don't know how to limit the rule only to the table itself. It looks...really ugly right now. >.<

  • can you paste your actual rules so we can replicate it exactly? – airstrike Jan 12 '17 at 19:59

Instead of your current rule for the red cells, I'd use a formula that checks if the cell is empty but the cell to the left of it isn't.

Assuming the contents of your table start on row 2 (i.e. your headings are on row 1): =AND(NOT(ISBLANK(B2)),ISBLANK(C2))

| improve this answer | |
  • 1
    I don’t understand what this answer is supposed to be doing. C4<>"" is just a different way of saying “cell does not contain a blank”.  And the question isn’t about cells that aren’t blank, it’s about cells that are blank. – Scott Jun 17 '19 at 22:10
  • @Scott I had misread the question, sorry. Edited to fix it. – airstrike Jun 18 '19 at 21:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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