Highlight Cells in Excel Table If Two Criteria Met

July 27, 2016 at 10:41:37
Specs: Windows 64
I have a table that is 10 columns(C-L) by 10 rows(3-12). (Only interested in data in 6 columns listed below.)
Using Data Validation, 2 columns (C,D) have possible values of Cat1, Cat2, Dog1, Dog2, or cell may be left blank.
Using Data Validation, 4 columns (E, G, J, L) have possible values of Delta1, Delta2, Delta3, Delta4, Echo1, Echo2, Echo3, Echo4, or the cell may be left blank.

If any given row where (Cat1 or Dog1 appears in C or D) and (Echo appears in E or G or J or L), I would like to highlight the cells in a row where those occur, but not the whole row.

For instance, if I have Cat1 in C3 and Echo2 in E3 and Echo4 in G3, I want C3, E3, and G3 to highlight cell in Yellow. I do not want C3,J3,L3 to get highlighted.

If Dog1 in D5 and Echo3 in J5, I want D5 and J5 highlighted, but not C5,E5,G5,L5.

If Cat1 or Dog1 appears in a row where Echo does not, no highlight in any cells in that row.
If Echo appears in a row where Cat1 or Dog1 does not, no highlight in any cells in that row.

Any Cat, Dog, Delta, and Echo values may show multiple times within the table.

Here's what I have (Row 3 is first row of selectable data).

Using Conditional Formatting > Manage Rules > New Rule > Use a formula to determine which cells to format

The 'Applies to' below is for Conditional Formatting where I highlight Yellow

Cat1/Dog1 and Echo column E
=OR(AND($C3="Cat1", COUNTIF($E3,"*Echo*")>0), AND($C3="Dog1",
COUNTIF($E3,"*Echo*")>0))
Applies to =$C$3:$C12,$E$3:$E$12
=OR(AND($D3="Cat1", COUNTIF($E3,"*Echo*")>0), AND($D3="Dog1",
COUNTIF($E3,"*Echo*")>0))
Applies to =$D$3:$D12,$E$3:$E$12

Cat1/Dog1 and Echo column G
=OR(AND($C3="Cat1", COUNTIF($G3,"*Echo*")>0), AND($C3="Dog1",
COUNTIF($G3,"*Echo*")>0))
Applies to =$C$3:$C12,$G$3:$G$12
=OR(AND($D3="Cat1", COUNTIF($G3,"*Echo*")>0), AND($D3="Dog1",
COUNTIF($G3,"*Echo*")>0))
Applies to =$D$3:$D12,$G$3:$G$12

Cat1/Dog1 and Echo column J
=OR(AND($C3="Cat1", COUNTIF($J3,"*Echo*")>0), AND($C3="Dog1",
COUNTIF($J3,"*Echo*")>0))
Applies to =$C$3:$C12,$J$3:$J$12
=OR(AND($D3="Cat1", COUNTIF($J3,"*Echo*")>0), AND($D3="Dog1",
COUNTIF($J3,"*Echo*")>0))
Applies to =$D$3:$D12,$J$3:$J$12

Cat1/Dog1 and Echo column L
=OR(AND($C3="Cat1", COUNTIF($L3,"*Echo*")>0), AND($C3="Dog1",
COUNTIF($L3,"*Echo*")>0))
Applies to =$C$3:$C12,$L$3:$L$12
=OR(AND($D3="Cat1", COUNTIF($L3,"*Echo*")>0), AND($D3="Dog1",
COUNTIF($L3,"*Echo*")>0))
Applies to =$D$3:$D12,$L$3:$L$12

This works, provided no typos when posting here. I just can't help but think there's a much easier (less Conditional Formatting statements) way to do this.

message edited by riv37


See More: Highlight Cells in Excel Table If Two Criteria Met

Report •


#1
July 27, 2016 at 11:25:01
         C       D       E       F       G       H       I       J       K       L

3       Cat1           Echo2           Echo4       

4

5              Dog1                                            Echo3

6

7

8

9

10

11

12      


Report •

#2
July 27, 2016 at 14:24:25
Something like this should work:

=AND(COUNTIF(C1:D1,"<>"&""),COUNTIF(E1:J1,"="&"Echo*"))

If cells in columns C & D is not blank it must contain Cat or Dog
so this part returns TRUE
and
If cells in columns E thur J equals Echo1 thru Echo 4
this part returns TRUE

Not tested, but I think the logic is correct.

MIKE

http://www.skeptic.com/


Report •

#3
July 27, 2016 at 17:22:00
Just got a chance to test the formula, and it does not work.

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
July 28, 2016 at 06:43:58
Thank you for looking at this.

The original title of my post made it seem like a really simple question. Unfortunately, it wouldn't allow me to edit the post title after the fact.

To clarify the above, the 1 is the key element of the string in Columns C&D.

Also, all cells (C3:D12) could be the same (i.e. Cat1 or Dog1 or Cat2 or Dog2)

I'd like to change the names used, but this is not an option.

Echo is the key element in the string in Columns E, G, J, L, but there is the probability of a 1 showing up in columns E,F,G,H,I,J,K,L.

Also, all cells (E3:E12,G3:G12,J3:J12,L3:L12) could be the same (i.e. Echo1 or Delta1 or Echo2 or Delta2, etc...)

This part was excluded in my original post. F,H,I,K are also Data Validated, but will NOT include Echo.

Ultimately, Cat1 and/or Dog1 is incompatible with Echo. I want to highlight the incompatible items. I don't want to restrict that incompatible items from appearing in the same line from happening as the table reflects a current condition as the items change.

message edited by riv37


Report •

#5
July 28, 2016 at 06:49:37
        C       D       E       F       G       H       I       J       K       L

3      Cat1           Echo2           Echo4       

4

5              Dog1                                           Echo3

6      Cat1    Cat1   Echo1           Echo1                   Echo1          Echo1

7

8

9

10

11

12      

This shows the possibility of multiple uses of same item.

message edited by riv37


Report •

Ask Question