Change color of cell in excel if all values of a list are present in different column?

0
votes

I am trying to change color of cell in excel if all values of a list are present in a different column.

This is my custom list with some values

I, also have another column with some values.

I want that color of myColumn cell should be red if all values of MyList are not present.

enter image description here

And it should be green if all values from MyList are present in myColumn

enter image description here

I tried doing it using conditional formatting, but it changes color of cell based on single cell value.

=INDEX(B2:H2,MATCH(D2,B13:B15,0))

How can I do it based on values of a list?

2
excelexcel-formulaconditional-formatting
can you please add another picture to show the expected format on your myColumn and explain why it is the case? Also please specify how did you set up the conditional formatting and maybe post the formula. - Terry W
@TerryW Added pictures as per your suggestion. I expect this because I want to check if I have covered all possible cases in my test which will be defined in myColumn. And all possible cases are predefined in MyList - atg

2 Answers

2
votes

Suppose you have the following named ranges:

  • MyList being Cell B2:H2 in my example;
  • myColumn being Cell B5:B7 in my example. Please note I have named this range in the form of a Table so when I add values to this column, new values are automatically added to the named range. Set the background color of the header myColumn to red.

named ranges

Then you can set the following formula as the conditional formatting rule for Cell B4 which is the header myColumn:

=SUMPRODUCT(ISNUMBER(MATCH(MyList,myColumn,0))*1)=COLUMNS(MyList)

MATCH will compare the values in MyList with myColumn and return a number for a match and #N/A for no match, ISNUMBER will convert the results to TRUE or FALSE. SUMPRODUCT will sum the results and if all values are matched it should return 7 in the above example. Then compare the summed result with the expected result COLUMNS(MyList) which is 7 as well. If match, the formula will return TRUE, which will then trigger the conditional format to turn the cell background colour to green.

Formatting Rule

If I add more values to myColumn to match MyList:

Result

Please note the formula only checks if all MyList values are present in myColumn but not the other way around, which means if your myColumn also contains other number such as 8, 9, 10 which are not in MyList, the header will still turn green as long as it contains all values from MyList regardless if it has extra values or not.

Let me know if you have any questions. Cheers :)

1
votes

Image

Check out the image: myrow has values that are VLOOKUP() against mycolumn and if isnumber() will return TRUE, otherwise FALSE. Then do an and() on all the resulting values and that result can be used to determine the color of the cell.

So by setting the cell's default fill color to RED, it will by default be RED, unless the following formatting rule is met, then it will be GREEN:

Image2