# Conditional formatting - identifying blanks cells based on another cell entry

#### Newbienew

##### Active Member
Hello Friends,

I am looking for an easier way to use a formula that I have in a broader sense. I currently use the formula in a column aspect which works well, however, I don't want to have to repeat the same thing 12 times to achieve my goal. The formula that I am using looks like this. =AND(COUNTIF(D4,""),COUNTA(B4)). This formula covers several cells in the B column. That same formula is used in column D just the B4 and D4 are switched. So for the above formula, I would have to do it 12 times to cover each month, D4,E5,E6 M6,N6.O6 V6,W6,X6, AE6,AF6,AG6 for it to work.

Added, for the switch version, I would have to do it for each month. Is there a better way to do this where it does not have to be entered so many times. Perhaps an array of sorts.
Please see the link below for an example version.. Columns B and D have the condition formatting. Thank you

### Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the \$ sign).

#### Jeffrey Mahoney

##### Well-known Member
Am I to assume that you want the cells to turn red if the user has not fully completed all 3 months of data?

If the HR Advisor has data, then there must be values in each month
If the any of the months has a value, then the remaining months and the HR Advisor must have values?

#### Jeffrey Mahoney

##### Well-known Member
I have two formulas for you to try

This one tests all 4 cells and determines that if any are not empty and not all of them are full then turn red
=AND(COUNTA(\$B4,\$D4:\$F4)>0,COUNTA(\$B4,\$D4:\$F4)<4)

This one tests if B is not empty and the month cells are not complete | along with if B is empty and the month cells have at least one value, then turn red.
=OR(AND(COUNTA(\$B4)=1,COUNTA(\$D4:\$F4)<3),AND(COUNTA(\$B4)=0,COUNTA(\$D4:\$F4)>0))

Using anchors (\$), you can have it test multiple columns. Hope that gets you closer. This can be used in one conditional formatting so you don't have to create a CF for each column.

#### Newbienew

##### Active Member
I attempted to use both but not sure if I understand them or how the works overall. Maybe I am using it incorrectly. When I did use it everything turned red.

#### Jeffrey Mahoney

##### Well-known Member
They are not to used together. When I use either, they work as described. When you say everything turned red, do you mean all the lines turned red even if the criteria wasn't met?

The first CF formula counts all values in columns B, D, E, and F. If any are not blank, but some are blank, then turn red.

The second CF formula has a two part test:
1: If B is not blank and any of D, E, and F are blank, then turn red
2: If B is blank and any of D, E, and F are not blank, then turn red.

For your affected range, you have the option of choosing any of the columns mentioned because of the anchoring. You do have to start your affected range on row 4 because that is the base row for the CF formula.

#### Newbienew

##### Active Member
I went thru and did the condition formating for all the columns and cells. Hopefully this will explain it better. I wanted to avoid doing the CF repeatedly as i did. I attempted to use your CF formula but struggled with trying to understand it. I believe the second formula would be what i need but not sure how to use it i guess. Conditional Format issue

Replies
29
Views
2K
Replies
2
Views
119
Replies
4
Views
68
Replies
4
Views
121
Replies
10
Views
458

1,127,116
Messages
5,622,816
Members
415,934
Latest member

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back