Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Gunjani
 
Posts: n/a
Default Conditional Format as a MACRO

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

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bob Phillips
 
Posts: n/a
Default Conditional Format as a MACRO

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" wrote in message
oups.com...
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



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Gunjani
 
Posts: n/a
Default Conditional Format as a MACRO

It does not work
Message compile Syntax error...

sent you an email

Rgds
Gunjani

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bob Phillips
 
Posts: n/a
Default Conditional Format as a MACRO

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)

"Gunjani" wrote in message
ups.com...
It does not work
Message compile Syntax error...

sent you an email

Rgds
Gunjani



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Conditional Format Not Working KMH Excel Discussion (Misc queries) 0 December 22nd 05 06:32 PM
Keep conditional format when "show pages" from Pivot table Angus Excel Discussion (Misc queries) 7 June 30th 05 01:33 PM
Formulae, conditional formatting & macro security Kevin Lucas Excel Discussion (Misc queries) 7 March 15th 05 02:10 PM
Conditional Format Titles Jenn Excel Discussion (Misc queries) 1 February 22nd 05 10:41 PM
Excel Macro Question about Conditional Formatting David Britton via OfficeKB.com New Users to Excel 3 February 10th 05 03:23 PM


All times are GMT +1. The time now is 04:45 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"