format named cells based on value of another cell

[sujimon]

Hi,

Here's what Iam trying to do..
I have 20 named cells/range and I need to change the color of these cells based on the value of another cell.
eg :- if value in A1 is AAA then change the color of cells named AAA
if value in A2 is BBB then change the color of cells named BBB
if value in A3 is XXX then change the color of cells named XXX

Thanks
Suji

[Andrew-R]

Select each range in turn and apply conditional formatting using the formulas:

=$A$1="AAA"

=$A$2="BBB"

=$A$3="XXX"

[sujimon]

the problem with that is A1 can have any value ...ie AAA or BBB or CCC or DDD etc ... similarly A2,A3,A4..... also can have any value AAA, BBB,CCC etc.. but there wont be any duplicates ie AAA will be used only once but can be in any of the cells.

[Andrew-R]

OK, still with conditional format, use the formula;

=NOT(ISNA(MATCH("AAA",$A$1:$A$4,0)))

Edited to add: Or,

=COUNTIF($A$1:$A$4,"XXX")>0

[sujimon]

Dear Andrew,

Thanks a lot. it worked.

One more question ... is it possible to merge cells using conditional formatting. reason being when I select a group of cells and apply conditional formatting, I want to change value in the cell and apply border only around the whole group of cells..
eg :- when I apply conditional formatting to F1:G4 .. I want F1:G4 merged with border around it and also add some text to it.

Thanks in advance,

Suji