Conditional formatting - identifying blanks cells based on another cell entry

Newbienew

Active Member
Joined
Mar 17, 2017
Messages
376
Office Version
  1. 2016
Platform
  1. Windows
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

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
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?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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

 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top