Conditional Formatting for multiple words

**HeebieGeebie** · 06-19-2012, 08:18 AM

Hi There!

Just a quick one! Any help greatly appreciated. I have a list of place names in a column, several hundred of them, and I want to highlight specific place names a specific colour.

So the list looks like this:

Ancona
Barcelona
Bari
Belfast
Bilbao
Bremerhaven
Brest
Brindisi
Cadiz
Cagliari
Cannes
Cartagena
Catania
Cefalonia
Civitavecchia
Cobh
Constantza
Copenhagen
Corfu
Dublin
Elsinore
Flamm
Gibraltar
Goteborg
Gran Canaria
Greenock
Hamburg

And say I wanted to shade the cells containing the names shown below in red:

Copenhagen
Corfu
Dublin
Elsinore
Flamm
Gibraltar
Goteborg
Gran Canaria

Is there a way to tell excel to find any cell that matches any cell in that range and shade it red?

Many thanks,

Adam

**jraj1106** · 06-19-2012, 08:28 AM

Hi

Try this:

SEARCH.xlsm

Regards..

**canapone** · 06-19-2012, 08:33 AM

Hi,

in the example I'm using conditional format in A1:A27

This is the formula to activate the red background

=SUM(COUNTIF(A1,$C$1:$C$8))

Hope it helps

**Fotis1991** · 06-19-2012, 08:44 AM

..Or using MATCH function in CF rules

=match($a1;$c$1:$c$8)

**HeebieGeebie** · 06-19-2012, 09:01 AM

Thank you all for your rapid responses!

They all work, I think i'll use conditional formatting as opposed to VBA though, but now I know how to do it in VBA aswell so I thank you none the less!

You're all so helpful!

Adam