Conditional Formatting using Match

**phantomshark** · 12-20-2018, 11:19 PM

So here is what I want to do. Column B is a list of names. Columns C and D are descriptive columns that are not important. Columns E-O are up to 10 more names that are linked to column B. Column B are all unique names, but E-O can have duplicates by different column Bs. I hope that is clear.

Anyway, what I want to do is conditionally format columns E-O so that if the name in the cell matches any in column B, it will highlight a different color. This way I can later go through and add the unhighlighted names to column B, giving them their own row. I assume this will be using the match command, but I can't figure out the formatting, nor how to apply the format to all the columns I wish. I'd also like to make it so if I enter a name into column B that is already there, it will turn red. I'd appreciate any advice.

**benishiryo** · 12-21-2018, 02:38 AM

welcome to the forum. this is for column E:O. select the range you want to apply to (say from E2:O10)
go to Home tab -> Conditional Formatting -> New Rule -> Use a formula to determine which cells to format -> Format values where this formula is true:
=COUNTIF($B:$B,E2)
format desired color

for column B, do the same steps but for B2:B10 for eg:
=COUNTIF($B:$B,B2)>1

if i guessed it wrongly, do upload an excel sample so that we do not have to manually key in your data to do a testing.

input the desired results so that we don't have to second-guess if what we are doing is correct or not. you may look at my signature to upload an eg that is easier to understand.

the upload attachment must be done by going to Go Advanced. click on Manage Attachments. Choose file, upload and close the window.

**phantomshark** · 12-21-2018, 08:35 PM

Oh, that is perfect, thank you. Wish I understood why that worked, but it's enough that it worked.

**protonLeah** · 12-21-2018, 08:42 PM

Conditional formatting formulas must result in True (greater than zero) or False (zero)
=COUNTIF($B:$B,E2)
counts the occurances of the value in E2 found in column B. If it's 1 or more True is returned and the CF is applied.

Conditional Formatting using Match

LinkBack

Thread Tools

Rate This Thread

Display

Conditional Formatting using Match

Re: Conditional Formatting using Match

Re: Conditional Formatting using Match

Re: Conditional Formatting using Match

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] No match conditional formatting

Conditional formatting with INDEX MATCH MATCH

3 way match with conditional formatting

Conditional formatting to match row numbers

using conditional formatting to match MONTH

Match and conditional formatting

MATCH, FIND & Conditional Formatting

Bookmarks

Bookmarks

Posting Permissions