CODEWITHSUNDEEP
excelconditional-formatting
Abhishek Kumar
Abhishek Kumar

Reputation: 352

Conditional Formatting for Blank Cell Does not Give Expected Result

I am trying to format a cell, the goal is to have a Green Fill if the adjacent cell (on left) is blank and Red Fill if not blank. Below is the Code applied to Column D

="ISBlank(($C$2)"

and a snapshot below target cell Snapshot of Ecel Implimentation

I am not getting the Desired result, the cells in column D remain white (excel default format of cells)

Please see the Data I am using below DATA

Upvotes: 0

Views: 1882

Answers (3)

pnuts
pnuts

Reputation: 59485

1] Do way with the double inverted commas
2] The Applies to range must be more than a single cell if you want the CF to apply across a range of more than one cell
3] You don't need two rules

Select ColumnD and 'standard' fill red. (Remove fill from D1 if preferred). Select ColumnD (this sets the Applies to range) and HOME > Styles - Conditional Formatting, New Rule..., Use a formula to determine which cells to format and Format values where this formula is true::

=ISBLANK(C1)

Format..., select green Fill, OK, OK.

You do not need to lock the column reference because you are only applying the CF to a single column. You must not lock the row reference if to apply to multiple rows on a row by row basis.

With the above if a cell is red and has an apparently empty cell on its left then that cell is not actually empty.

Upvotes: 0

maxhob17
maxhob17

Reputation: 612

I don't think you need the $ before the 2 in =ISBLANK($C$2) otherwisethe conditional formatting will only refer to C2. I think you'll want:

=ISBLANK($C2)

Also if the contents in column C is ="" , ISBLANK will return FALSE, assuming you don't want that behavior, you could try:

=LEN($C2)>0

Which checks how long the text is in C2.

Example:

enter image description here

Upvotes: 1

Andrew L
Andrew L

Reputation: 7038

Occasionally I've also had trouble getting is blank() to work properly. What I would try is:

$d$2 = ""

Upvotes: 0

Related Questions