Conditional formatting - identifying blanks cells based on another cell entry

Newbienew

Board Regular
Joined
Mar 17, 2017
Messages
185
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

 

Jeffrey Mahoney

Well-known Member
Joined
May 31, 2015
Messages
1,604
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
Joined
May 31, 2015
Messages
1,604
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

Board Regular
Joined
Mar 17, 2017
Messages
185
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
Joined
May 31, 2015
Messages
1,604
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

Board Regular
Joined
Mar 17, 2017
Messages
185
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

 

Forum statistics

Threads
1,082,243
Messages
5,363,972
Members
400,772
Latest member
solbebe

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top