# Conditional Formatting in Excel2010

Solved/Closed
pjevans Posts 1 Registration date Friday July 26, 2013 Status Member Last seen July 27, 2013 - Jul 27, 2013 at 12:07 AM
Peter - Jul 30, 2013 at 05:55 PM
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.

Peter

## 1 response

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Jul 30, 2013 at 10:55 AM
Hi Peter,

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
Hi Trowa, thank you and as you expected....the vba works....awesome.

Regards
Peter