Pls help - Excel - Highlight cell if no data

ems - Jun 10, 2009 at 12:27 PM
 joe random - Oct 5, 2010 at 11:31 PM
Please could somebody help me with a problem. I have a spreadheet and I would like make some cells in a row go yellow if they have no data in them. the data will either be text or a date. This is to highlight if ALL information on a spreadsheet row has NOT been entered or has been left out (the row info will be entered over a period of time)

If this cannot be done do you know how to just make one cell turn yellow if there is no date entered?

Thank you for any help


2 responses

try using conditional formatting
I have tried using conditional formatting but I can't figure this out.

I have daily logs that track issues/problems with our equipment. I have a cell that we enter in the time/data the issue was fixed. What I want to do is have the rows without any information in the time/date cell highlight a specific color.

If this can be done in conditional formatting please tell me how.

Assume the cell you want conditional formatting on is cell D9.
select cell D9.
Select Format > Conditional Formatting... menu option
In the Conditional Formatting dialog, change the 1st drop down from "Cell Value Is" to "Formula Is"
In the formula enter "=IF(LEN(TRIM(D9)) = 0, TRUE, FALSE)" without the quotes.
Then select the Format button and select the Patterns Tab. Select the color you want for the background of the cell.

If you want to highlight the whole row based on the value of this cell, change D9 to $D9. Then copy cell D9, select the row, right click and "Paste Special...". Select Formats checkbox and click Ok.

To apply this to all of the other Rows, select row 9 and copy it. Select all of the rows and "Paste Special...". Select Formats checkbox and click Ok.

You can do it like this, no more complicated -
Go to -
1. Format
2. Condtional Formatting
3.Condition1 Cell Value Is equal to, here you enter value 0 (zero).
4. Click on Format button, click on Patterns and now select color of your choice.
5. Click OK.
6. Once again you click OK.

I think this should solve your problem.

- Chandra
Murdergirl > Chandra
Dec 2, 2009 at 01:22 PM
No so much. All your formula does is color the individual cells, not the entire row based on a particular cell value. I tried both of your suggestions, and neither worked. The previous formula DID highlight the entire row, but randomly and NOT based on a given value for a cell within that row. YOUR formula simply highlighted every cell that met my condition. I want to color the entire ROW when a cell in that row of Column N is a 1, and ignore it if it's not a value of 1. You'd think this would be easy enough to do, but I spend more time here trying all these suggestions that just typing random crap into Conditional Formatting and finally hitting on something that works. Thanks for the suggestion, but no dice....SOMETHING is missing.
chris > Murdergirl
Feb 2, 2010 at 04:12 PM
I realize this is old but...if you want an entire row to be highlighted when column N has a value of then do this for your conditional formula:


That's it.
lol... thanks chris, helped me.