Dynamic conditional formatting

Jacko1307

Board Regular
Joined
Sep 4, 2012
Messages
85
Office Version
  1. 2016
Platform
  1. Windows
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. Conditional.JPG
Conditional.JPG
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,871
Office Version
  1. 365
Platform
  1. Windows
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
3 CF rules.jpg


Result
3 CF rules - OUTPUT.jpg


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

Board Regular
Joined
Sep 4, 2012
Messages
85
Office Version
  1. 2016
Platform
  1. Windows
Brilliant, thank you works fine.

Thank you so much for your help.

I was trying to over complicate things in the formula :(

Regards,

Martin
 

Watch MrExcel Video

Forum statistics

Threads
1,114,521
Messages
5,548,542
Members
410,846
Latest member
rafferty1702
Top