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.

Thankyou in advance and regards
Peter

1 reply

TrowaD
Posts
2880
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 2, 2022
509
Jul 30, 2013 at 10:55 AM
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
0
Hi Trowa, thank you and as you expected....the vba works....awesome.

Regards
Peter
0