11

2 Columns.

C2 | D2  
C3 | D3  
C4 | D4  
C5 | D5  

I want it to apply conditional formatting to D2 if C2 has information in it, and D2 is blank. (will be a date). If C2 is empty, then D2 should have no formatting. I don't want D2 to be formatted if C2 has text, and D2 has text.

Picture: I would want it to look like this:

Example

18

The ISBLANK formula should serve your purpose well.

Create a new rule in the conditional formatting menu. Set the range of your rule to D2:D so that the formatting is applied to column D. Then, in the Condition dropdown menu, select "Custom formula is." In the input field, enter =NOT(ISBLANK(C2:C)). (The = implies the rest is a formula. The NOT formula negates the value returned by its argument. Since you want the formatting rule to apply if the corresponding cell in column C is NOT empty, you will need to use this.) Finally, set the formatting to suit your needs.

| improve this answer | |
  • 3
    Worked very well. I ended up using " =AND(NOT(ISBLANK(C2:C)),(ISBLANK(D2:D))) ". Because I didn't want the D column to be formatted if it had text.(Forgot to include that in my message). Thank you very much :) – Aethroz Oct 7 '15 at 18:48
  • 6
    Critical to have the = in front of the conditional statement. So =NOT(ISBLANK(C2:C)) works, but NOT(ISBLANK(C2:C)) will not. Hopefully saves some people some time! Google Sheets is so powerful but definitely has some documentation concerns especially for folks coming from Excel. – Shane Oct 23 '17 at 12:02
  • 1
    UPDATE - Now conditional formatting has a "Cell is empty" condition as a provided option. – Matt Jun 20 '18 at 15:24
  • 1
    But remember, sometimes a cell isn't quite empty, and isn't quite blank, but still might be =="" so C2:C=="" might be a good check when the others don't seem to work. – Victoria Aug 9 '18 at 5:29
  • Sometimes a cell isn't quite empty, so if NOT(ISBLANK(A2)) is not working, highlight the offending cell (which looks empty but is seemingly erroneously highlighted) and press delete (or backspace). That just worked for me. I found this question & answer because I was driving myself crazy thinking the formula is logically correct and that I did not see anything inside this cell (A2), yet it was highlighted. As soon as I "deleted" the supposed contents, the cell ceased being highlighted. – user664833 Jun 13 at 19:57

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