Conditional Format as a MACRO

[Gunjani]

I'm still having difficulty with Conditionl Format....

I like to apply the following...
In Column G
If cell equals number between 1 to 29 Colour Yellow on same row from
Column E to Column I
If cell equals number between 30 to 49 Colour Green on same row from
Column E to Column I
If cell equals number between 50 to 69 Colour Blue on same row from
Column E to Column I
If cell equals number between 70 to 89 Colour Mauve on same row from
Column E to Column I
If cell equals number between 90 to 99 Colour on same row from Column E
to Column I
If cell equals(or contains) "STR" Colour Orange on same row from Column
E to Column I
If cell equals(or contains) "Spare" Colour Grey on same row from
Column E to Column I
If cell contains "Shunting" Colour Dark Green on same row from Column E
to Column I
If cell contains "Patrols" Colour Pink on same row from Column E to
Coloumn I
If cell contains ">" Colour Red ONLY That Cell

In Column M
If cell equals number between 1 to 29 Colour Yellow on same row from
Column K to Column O
If cell equals number between 30 to 49 Colour Green on same row from
Column K to Column O
If cell equals number between 50 to 69 Colour Blue on same row from
Column K to Column O
If cell equals number between 70 to 89 Colour Mauve on same row from
Column K to Column O
If cell equals number between 90 to 99 Colour on same row from Column K
to Column O
If cell equals(or contains) "STR" Colour Orange on same row from Column
K to Column O
If cell equals(or contains) "Spare" Colour Grey on same row from Column
K to Column O
If cell contains "Shunting" Colour Dark Green on same row from Column K
to Column O
If cell contains "Patrols" Colour Pink on same row from Column K to
Column O
If cell contains ">" Colour Red ONLY That Cell

As it would need to be created in a Macro and I have no Idea on where
to start with Macro can u provide a step by step guide PLEASE

An Idiots guide on Macro would be great!! As I may need to make
additions later.

Many Many Thanks

Gunjani

 

[Bob Phillips]

Option Explicit



Private Const xlCIBlack As Long = 1
Private Const xlCIWhite As Long = 2
Private Const xlCIRed As Long = 3
Private Const xlCIBrightGreen As Long = 4
Private Const xlCIBlue As Long = 5
Private Const xlCIYellow As Long = 6
Private Const xlCIPink As Long = 7
Private Const xlCITurquoise As Long = 8
Private Const xlCIDarkRed As Long = 9
Private Const xlCIGreen As Long = 10
Private Const xlCIDarkBlue As Long = 11
Private Const xlCIDarkYellow As Long = 12
Private Const xlCIViolet As Long = 13
Private Const xlCITeal As Long = 14
Private Const xlCIGray25 As Long = 15
Private Const xlCIGray50 As Long = 16
Private Const xlCIPeriwinkle As Long = 17
Private Const xlCIPlum As Long = 18
Private Const xlCIIvory As Long = 19
Private Const xlCILightTurquoise As Long = 20
Private Const xlCIDarkPurple As Long = 21
Private Const xlCICoral As Long = 22
Private Const xlCIOceanBlue As Long = 23
Private Const xlCIIceBlue As Long = 24
Private Const xlCISkyBlue As Long = 33
Private Const xlCILightGreen As Long = 35
Private Const xlCILightYellow As Long = 36
Private Const xlCIPaleBlue As Long = 37
Private Const xlCIRose As Long = 38
Private Const xlCILavender As Long = 39
Private Const xlCITan As Long = 40
Private Const xlCILightBlue As Long = 41
Private Const xlCIAqua As Long = 42
Private Const xlCILime As Long = 43
Private Const xlCIGold As Long = 44
Private Const xlCILightOrange As Long = 45
Private Const xlCIOrange As Long = 46
Private Const xlCIBlueGray As Long = 47
Private Const xlCIGray40 As Long = 48
Private Const xlCIDarkTeal As Long = 49
Private Const xlCISeaGreen As Long = 50
Private Const xlCIDarkGreen As Long = 51
Private Const xlCIBrown As Long = 53
Private Const xlCIIndigo As Long = 55
Private Const xlCIGray80 As Long = 56

'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "G29"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Value
Case Is < 0: 'nothing
Case Is < 30: Me.Range("E" & .Row).Resize(,
5).Interior.ColorIndex = xlCIYellow
Case Is < 50: Me.Range("E" & .Row).Resize(,
5).Interior.ColorIndex = xlCIGreen
Case Is < 70: Me.Range("E" & .Row).Resize(,
5).Interior.ColorIndex = xlCIBlue
Case Is < 90: Me.Range("E" & .Row).Resize(,
5).Interior.ColorIndex = xlCIViolet
'etc.
Case Is = "STR": Me.Range("E" & .Row).Resize(,
5).Interior.ColorIndex = xlCIOrange
'etc.
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.



--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Gunjani]

It does not work
Message compile Syntax error...

sent you an email

Rgds
Gunjani

 

[Bob Phillips]

It is wrap-around, the lines like

Case Is < 30: Me.Range("E" & .Row).Resize(,
5).Interior.ColorIndex = xlCIYellow

should all be on one line.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)