Conditional formatting for "not february"

[mistah_q]

I am constructing a workbook with 12 worksheets, one for each month of the year, and I can't seem to crack this:

Conditional formatting - highlight entire row if month is not X. The reason - so it's highlighted if an entry is put on the wrong month's tab.

=MONTH($H1)<>1

Works just fine on the January worksheet. But

=MONTH($H1)<>2

2, or 3, or any other month... it doesn't work on the other tabs. Instead it turns my entire sheet yellow.

I believe what's happening is that Excel counts any old blank cell as "January 0" so trying to use a "if cell is not february" (or march etc) will just light up my entire worksheet like a christmas tree.

Is there some way around this?

 

[shg]

How about =ISNUMBER($H1) * (MONTH($H1)<>1)

I'd be reluctant to apply CF to an entire sheet.

 

[shg]

Missing a pair of parens in that, corrected above.

 

[mistah_q]

Hmm, with your correction added, this works. Thank you.

I discovered another solution - an additional rule of:

=$H1=DATE(1900,1,0) with no formatting that must be above the =MONTH($H1)<>2 rule but below other rules... your solution works more elegantly.

 

[shg]

You're welcome.