Conditional formatting empty cells based on full cells

[kellem80]

Hi,
Working in Excel 2007.
I am using excel for a data log (basically) and want it to format all empty cells in a row yellow if there is data in column A

Basically, If i have a value in A2, I want any empty cell between B2-G2 to be filled in yellow (as an idicator to the inputter that the cell needs to be completed).

there is already conditional formatting on these cells, which i want to maintain for the non-empty cells. I also have "0" as a value, so I couldn't use the basic conditional formatting setting it =0, it highlighted cells with $0.00, which i do not want.

Thanks in advance!!!

 

[sfsteve002]

Hi, try this.

Highlight cells B2-G2

-> Conditional Formatting -> Formulas and use the formula

=$A$2<>""

 

[kellem80]

Thanks, it works initially, but when I enter data in the cell, it keeps the formatting (I want it to revert back to another already set conditional formatting based on a value in another cell). I tried changing the order of the rules and no order change gave me back my original formatting.

Thoughts on getting around this?

 

[sfsteve002]

Give this a try.

Add this rule to the conditional formatting for Cell B2 to G2.

=AND($A$2<>"",B$2<>"")

 

[Peter_SSs]

If you are using this over multiple rows, I think you will want to remove the $ from in front of the 2. Also, guessing a bit, but possibly this for the 'yellow' rule, select B2:Gxx and apply one of these rules.

=AND($A2<>"",B2="")
This should make a cell yellow if it is blank but column A is not

=AND($A2<>"",COUNTA($B2:$G2)=0)
This should make the cell yellow if column A is not blank and columns B:G are all blank in that row.


If none of the suggestion do what you want, please be more specific about what this means "I want it to revert back to another already set conditional formatting based on a value in another cell"

 

[kellem80]

WORKED!! (I used the first of the two options)

Thanks so much!!