Excel Conditional formatting whole table color code

Question

I would like to create a condition for my whole table.

I have three categories (i.e R, G, B).

If the first column (example: A1) is R, the rest of the ROW (ex: B1:E1) should be colored with red. If G, then green, and if B, then Blue.

What I've done so far is I've selected my work area which is B1:E1, then clicked on conditional formatting > new rule > "Use a formula to determine which cell to format" (I'm using excel 2010). Then Typed this at the formula bar =SEARCH("R", $A$1)>0. It does change the color of B1:E1, however, I don't know how to do this for my whole table. If I select my whole table, and A1 is equal to R or G or B, the whole table change its color to whatever the value of A1. I also change my formula to =SEARCH("R", $A$1:$A$120)>0 which is the range of my table, still doesn't work. I've also created 3 conditions for each color with same formula (I just changed the letter R to G and B), still does not work.

Any ideas? Thank you in advance.

Answer 1

you were actually pretty close, just change

=SEARCH("R", $A$1)>0

to

=SEARCH("R", $A1)>0

so the conditional formatting rule will always look for "R" in the current row rather than row 1. You should have this rule apply to the whole range that may be colored (I'm guessing B1:E120)

Also, you can make two rules for your other colors using the same approach.