conditional formatting excel 2013

Question

Is there a way to highlight a cell (possibly with conditional formatting) if one of the cells to the left of it has data, but it is empty?

for instance,

     A     B     C     D     
   ________________________
1 |     |     |     |  x  |
2 |     |  x  |     |     |
3 |  x  |     |     |     |
4 |     |     |     |     |
5 |     |     |     |     |

In the example, cells C2, D2, B3, C3, D3 would be highlighted because they are empty but there is a value in a cell to the left of it

Answer 1

Select the range you want to apply the conditional formatting rule to and go to Conditional Formatting on the Home ribbon, then New Rule.... From there choose Use a formula to determine which cells to format and enter the formula below.

=AND(COUNTA($A1:A1)=1,A1="")

Here A1 is the top-left cell of the selected range. Adjust the formula to fit your needs.

Next set the desired format/highlighting and click OK.

Answer 2

To highlight the cells in col B which appear next to a blank cell in col A:

• Put the cursor on B1 and select Conditional Formatting -> New rule.
• Select the last option in the list of rule types, "Use a formula to determine..."
• In the field that says "Format values where this formula is true:" enter "=IF(ISBLANK(A1),TRUE,FALSE)" (see screenshot)
• Click the format button to select exactly how you want the highlighted cells to appear
• Click OK, then OK again to exit the Rules Manager.
• Right click on B2 and select the Format painter (see screenshot) then drag down the column to apply that rule to the rest of the column as needed.