Using conditional formatting to change cell color if the name in the cells matches a range

**fablhof** · 07-01-2013, 04:42 PM

I'd like to match the name in one group of cells to a list of names and if the name is matched, have the color of the cell change to a specific color. Can that be done in conditional formatting? The attached spreadsheet is a sample of what I'm trying to do.

format example.jpg

Thanks for the help

**Pete_UK** · 07-01-2013, 04:59 PM

Select F12:N23, then click on Conditional Formatting | New Rule and choose Use a Formula …
Enter this formula in the box:
=ISNUMBER(MATCH(F12,$B$13:$B$21,0))
Then click on the Format button | Fill tab and choose your colour for that range of names (green)

Then repeat, but change the range in the MATCH function (in red above) and choose the colour for that range, and so on. Once you OK your way out of the CF dialogue box, Excel will adjust the cell references automatically.

I've done this for two colours in the attached file.

Hope this helps.

Pete

**BB1972** · 07-01-2013, 05:01 PM

fablhof, I think you're going to need to get busy with CF rules....

Select a cell (eg F14), go to the ribbon, click on Conditional Formatting, click on Highlight Cells Rules, click on "more rules...", click on "Format only cells that contain", select Format only cells with cell value equal to, click into the third field to make it active, then click on (in this case) cell B32. Click on format, fill, select your colour, the "Ok" your way out.

Click on F14 again, click on the Format Painter brush, then paste the format over all the names (in case there may be duplicates - if you're sure there aren't, or won't be, you needn't bother with the last step.)

I've done a few to get you started - hope this helps.

EDIT: or just do it Pete's way! That's why he's an expert

Using conditional formatting to change cell color if the name in the cells matches a range

LinkBack

Thread Tools

Rate This Thread

Display

Using conditional formatting to change cell color if the name in the cells matches a range

Re: Using conditional formatting to change cell color if the name in the cells matches a r

Re: Using conditional formatting to change cell color if the name in the cells matches a r

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions