Dynamic conditional formatting

[Jacko1307]

I have a live workbook which, is updated weekly with information.

Is there a way to apply a conditional format to the worksheet based on criterion in Column i which has a drop down list of 3 options.

Option 1 - COPF, Option 2 - EOPF, Option 3 - LIG.

The worksheet uses the columns A - to - N

I am trying to find a way to add different colors against each of the 3 criteria ( Amber - COPF, Light Blue to EOPF & Light Green to LIG)
I only want the background color to be applied to each Row in cells A-N.

 

[Yongle]

In your picture it appears that column A is empty and that COPF, EOPF and LIF are in column I
- if that is not the case, amend the formulas

Example uses 3 CF rules as in image

Result

To create CF rules per above example
1. Select cells A2:N2000 (amend to match required range)
2. Conditional formatting \ New Rule \ Use formula to determine which cells to format
3. the formula (see NOTES)
=$I2="COPF"
4. Select amber background colour from formatting options
5. Confirm with OK
6 Repeat for EOPF and LIF

NOTES
The formula used must be correct for the FIRST CELL in range (in above example that cell is A2)
The value being tested is in cell I2
The column is locked with $I because the value tested is always in column I
The 2 is NOT locked because the value tested is in the same row as the cell

 

[Jacko1307]

Brilliant, thank you works fine.

Thank you so much for your help.

I was trying to over complicate things in the formula

Regards,

Martin

 

[Yongle]