Conditional Formatting in Excel2010Solved/Closed

Question

I wish to have the following CF in more than three colours.


I have placed the following in Data List

L1,L2,L3,L4,L5,L6,M7,M8,M9,M10,M11,H12,H13,H14,H15,H16,H17,H18,H19,E20,E21,E22,E23,E24,E25

I wish to have L1,L2,L3,L4,L5,L6 cell turn green (43) when selected from the list

I wish to have M7,M8,M9,M10,M11 cell turn yellow (6) when selected from the list

I wish to have H12,H13,H14,H15,H16,H17,H18,H19 cell turn orange (44) when selected from the list

I wish to have E20,E21,E22,E23,E24,E25 cell turn red (3) when selected from the list

If I could have a simple answer it would be appreciated.

Thankyou in advance and regards
Peter

TrowaD · Answer

Hi Peter,

Right-click your sheets tab.
Click "View code".
A new window appears showing a big white field.
Paste the code in that field.
Check the green text to adjust the location of the data list.
Now you can close the code window and see if the Data list cell changes colors appropriately.

Here is the code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim mR As Range
Set mR = Range("A5") 'Change range to match yours
If Intersect(Target, mR) Is Nothing Then Exit Sub
If Left(mR, 1) = "L" Then mR.Interior.ColorIndex = 43
If Left(mR, 1) = "M" Then mR.Interior.ColorIndex = 6
If Left(mR, 1) = "H" Then mR.Interior.ColorIndex = 44
If Left(mR, 1) = "E" Then mR.Interior.ColorIndex = 3
End Sub
I kept it as simple as possible.

Best regards,
Trowa

Conditional Formatting in Excel2010

1 reply

Subscribe To Our Newsletter!