Cell color based on contents

[AMK4]

Hi folks,

I'm maintaining a list of orders in a custom sheet with the following columns:
Name - Email - Product - Cost - Order Date - Status

What I'd like to do is change the cell background color on all the BLANK cells in the Order Date column. However, if I use Conditional Formatting to select the full column, it will highlight ALL cells that are blank, including those that wouldn't have anything there because I haven't filled anything past the last row. In other words, if I only have 20 items on the sheet, the Conditional Formatting will still highlight the entire column.

I could tell Conditional Formatting to only use the range of 20 items I have on the sheet, however whenever I add more rows, I'd have to go adjust the range again, each time.

So is there a way to use Conditional Formatting to achieve this where it only works with the current range of items and then adjusts as I add more columns?

 

[iggydarsa]

Assuming "Name" is column A, and "Order" is column E then
- highlight column E
- Select Conditional Formatting > New Rule
- Select "Use a formula to determine which cells to format"
- Type =AND(A1<>"", E1="")
- click on format button and choose the desired format
- click OK

 

[AMK4]

Ah, that works beautifully. Thank you so much!

 

[iggydarsa]

 

[AMK4]

Question: what if I want to leave the first 10-15 ROWS empty? I'd like to put a running chart at the top of the data listed. But the above condition will then also color those cells because the referenced columns are blank. I suppose I can cheat and add something in those columns since it's behind the chart anyway, but I don't suppose that's the proper way of doing it.

 

[iggydarsa]

ignores first 10 rows

=AND(A1<>"", E1="", ROW(E1)>10)