CODEWITHSUNDEEP
excelexcel-formulaconditional-formatting
atg
atg

Reputation: 214

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

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?

Upvotes: 0

Views: 2652

Answers (2)

Terry W
Terry W

Reputation: 3257

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 :)

Upvotes: 2

Tyler M
Tyler M

Reputation: 412

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

Upvotes: 1

Related Questions