Based on cell value, locate a cell in a specific location and colour

[Jackman1]

Hi everyone,

Help! scratching my head all day!

I'm looking for a conditional format formula which would result in the attached image;
If "Tentative" (as DB4), colour CP4 based on the value in B4.
In English, If a Cell is "Tentative", look back 12 cells (based on changeable figure in B4) and colour accordingly.

I can add a hidden reference row if required in Row 5

As always thank you for any help you can offer, I'm trying to learn

 

[Fluff]

How about

=AND($DB4="Tentative",COLUMN($DB4:$DB4)-COLUMN()=$B4)

 

[FormR]

Hi, you could try, using the with a formula method.

=OFFSET(G4,0,$B$4,1,1)="Tentative"

You'd need to change the column to the first column in the range you want to conditionally format.

 

[Jackman1]

Thanks guys,

Fluff, the issue is the Tentative location will always move. Thanks for looking it though.

FormR, worked a treat, thank you!

 

[Fluff]

Glad we could help & thanks for the feedback.