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,

Share the link from where I can download your workbook.

Answer 3

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.

Hi John,

Ashish's suggestion works for me.

Are you sure that you included the two $ characters in Ashish's  CF formula?

===

Regards,

Norman

Answer 4

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.

Do like this -

1. Home tab > Conditional Formatting > Clear Rules > Clear Rules from Entire Sheet (This will clear previous rules)
2. Select your entire range (Or select rows in case you want to highlight the row till last column. Don't do CTRL+A as your sheet will become very heavy as this rule gets applied to entire sheet)

3. Home tab > Conditional Formatting > New Rule > Use a formula to determine which cells to format and put following formula of Ashish Mathur which is and select the required colour from Fill tab

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