Conditional Formatting question

[Jay]

I have a row of 8 cells, where the user chooses to fill one of the cells to
indicate a yearly quarter. The user then enters either R,A or G in the next
cell to indicate a status (red, amber or green).

I then want the cell filled to change to the colour indicated by the letter.
My question is - how can I conditionally format the fill dependent upon the
letter (R, A or G)? Because the cell filled isn't a fixed cell I'm not sure
how to approach the conditional formatting.

Any help greatly appreciated.

Thanks,

Jason

 

[Max]

Assume the cells to be conditionally formatted are A1:A8
with A9 containing either R, A or G

Select A1:A8 (A1 active), then apply CF using "Formula is"

Condition 1 :
=$A$9="R"
Format: Red fill

Condition 2 :
=$A$9="A"
Format: Amber fill

Condition 3 :
=$A$9="G"
Format: Green fill
Ok out

 

[Joerg]

Example: If you want to format A1 (the cell with your yearly quarter), the
formula for your conditional formatting would be =B1="R" (you would then
select a red fill color and add conditions for the other 2 letters...you are
lucky that in total you need only 3 formats, the maximum that Excel allows).

Now I'm not sure what your concerns about the "fixed cell" are. The address
you used when creating the conditional format will be automatically
adjusted, so don't worry. It looks absolute, but actually it's not.

Cheers,

Joerg

 

[Max]

Oops, dismiss the earlier, think I read it wrongly

Here's a re-take ..

Assume the cells to be conditionally formatted are A1:H1
Select A1:H1 (A1 active), then apply CF using "Formula is"

Condition 1 :
=OFFSET(A1,,1,)="R"
Format: Red fill

Condition 2 :
=OFFSET(A1,,1,)="A"
Format: Amber fill

Condition 3 :
=OFFSET(A1,,1,)="G"
Format: Green fill
Ok out