Ignoring Blank Cells with Excel Conditional Formatting

Question

Below, I have posted a sample excerpt from a spreadsheet I have created to track project completion progress. I have applied conditional formatting to highlight a row in grey if the date in the "completion date" column is prior to today's date. The formula I use is =$J4<TODAY(). So far, so good.

Problem: I want to maintain a few rows of blank cells (as seen below) between each engineer's programs. However, the conditional formatting applies itself to the blank cells and turns them all grey; I don't want the conditional formatting to touch those cells, I want them blank. I only want it to apply to rows with past dates in them.

I've tried using "format only cells that contain" to ignore the blank cells, and setting it as rule 1 with "stop if true" selected, and it partially works - at least the blank cells are left alone. However, rule 2, where the =$J4<TODAY() is applied, only ends up highlighting the pertinent dates in the J column, instead of highlighting the entire row like I want. I've confirmed that this formula does affect the entire row properly when used by itself.

Is there some way in conditional formatting to highlight the entire completed rows in grey like I want, while leaving the blank cells alone? 

Project Engineer	Program	Type	Project Number	Platform	Baseline	Contingency	# Shifts	Work Week	Completion Date	S/W Version

Engineer 1	(Customer)	(type)	#	(platform)	(version)	5 days	2	5 days	11/4/14	(version)
Engineer 1	(Customer)	(type)	#	(platform)	(version)	5 days	2	5 days	1/2/15	(version)
Engineer 1	(Customer)	(type)	#	(platform)	(version)	5 days	2	5 days	4/1/15	(version)
Engineer 2	(Customer)	(type)	#	(platform)	(version)	10 days	2	5 days	1/28/15	(version)
Engineer 2	(Customer)	(type)	#	(platform)	(version)	10 days	1	5 days	4/16/15	(version)

Answer 1

The formula worked for me (slightly changed by adjusting it to my case) for an Aged Payables report.  I wanted invoices that were >90 days and is ignoring blank cells.

Thank you.

Answer 2

Hi,

Edit your Conditional formatting formula to:

=AND($A4<>"",$J4<TODAY())

Hope this helps.

Answer 3

Hi,

Thanks for the insight; that's certainly less clumsy than my solution. It's still only highlighting the J column items though; I need it to highlight the entire row.

Answer 4

Hi,

Try this

1. Clear all conditional formatting from the worksheet
2. Write the above conditional formatting formula in cell A4 and choose your desired formatting.  Click on OK/Apply
3. Copy cell A4, select A4:L[last row of data]
4. Right click > Paste Special > Formats > OK

Hope this helps.

Answer 5

Ok, I tried your suggestion, and it made the entire selection (i.e. the whole spreadsheet) grey. Tried again, this time checked "skip blanks", and it did nothing; no highlighting at all. I feel like I'm missing something.