Conditional Formatting for multiple cells based on other cell value

[Benong]

Data value : A or B or blank
Range : A1 to Z6

Conditional Formatting
Range A11 to Z16
Change background colour A (Red), B (Blue)
A11 background colour will change base on A1 cell value.
A12 background colour will change base on A2 cell value.
A13 base on A3.
A14 base on A4.
A15 base on A5.
A16 base on A6….

Currently I create 2 rules (conditional formatting) for 1 cell. If I want to complete for all cells, I’ll have to create many rules. Is there an easy way to accomplish this task for so many cells?

 

[awoohaw]

Have you tried using the format painter? If that doesn't work, please post the rules you have for the top row of conditionally formatted cells? Or use the xl2bb add in to post a mini sheet (Link below). Thanks in advance.

 

[Joe4]

Welcome to the Board!

A11 background colour will change base on A1 cell value.
A12 background colour will change base on A2 cell value.
A13 base on A3.
A14 base on A4.
A15 base on A5.
A16 base on A6….

You haven't given us exactly what the rules are, but it is is easy to make cell A11 look at A1, A12, look at cell A2, etc, if you use the Formula option for Conditional Formatting rules.

For example, if you first selected cells A11:A16, and then enter a Conditional Formatting rule something like:

=A1

cell A11 will compare to cell A1,
cell A12 will compare to cell A2,
etc.

I doubt you want to check to see if the two cells are exactly equal, but since we do not know your exact logic/rule, I just chose this simple example for demonstration purposes.

 

[Benong]

I’ve managed to solve it base on the other thread online with similar situation. Answer is without using the $ sign in my formula.

 

[Benong]

Many thanks for assisting