Forum Discussion
Color-formatting a cell based on list of values across multiple columns
Got a complex one that has stumped my ability to Google. I've got a list of zip codes, subdivided into regions, and each region has a color. I want to put an address into a conditionally-formatted cell and have the cell change to the corresponding region color. So what I need is "If any text in C2:G17 matches any text in A25:A75 = color1; If any text in C2:G17 matches any text in B25:B75 = color2; If ... color8"
3 Replies
1 color = 1 rule. 8 colors will = 8 rules. Unfortunately, there's no getting around that but here's a sample workbook with 3 colors to get you started.
I strongly recommend creating named items for your ranges (e.g. A25:A76, B25:B75, etc.). It will make the rule creation a lot quicker.
My demo uses zip codes in Hawaii. The conditional formatting rule is a simple XMATCH. For example:
=XMATCH(C2,hawaii)Patrick2788 that helped a lot, thank you. I was unable to get the formula to work by typing in the name of the column, but I'm also using Google Sheets rather than actual Excel, does that make a difference? I was able to make it work with =XMATCH(C2,B25:B75,0,1)
- It might make a difference. I'm not at familiar with Sheets so I can't advise in that area.
