Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
urgent!!!!!!
I have the following code that changes the colour of a cell depending on the
cells value. Here is my code. Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Range("G8:BC95")) Is Nothing Then With Target Select Case .Value Case 0: Target.Interior.ColorIndex = 2 Case 1: Target.Interior.ColorIndex = 6 Case 2: Target.Interior.ColorIndex = 3 Case 3: Target.Interior.ColorIndex = 43 Case 4: Target.Interior.ColorIndex = 41 End Select End With End If ws_exit: Application.EnableEvents = True End Sub The problem i have is the fields are already populated and i want to be able to loop through these values and update the cell colours. Can anyone help????? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
urgent!!!!!!
The subject would be more useful if you put 'Cell color problem'
URGENT gives us NO information and is unlikely to provoke a quick response. Change the procedure name to something like Sub AmendColors() then move it into a standard module...the changes are in the code below: OPTION EXPLICIT Sub AmendColors() dim cell as range for each cell in Range("G8:BC95").cells With cell Select Case .Value Case 0: .Interior.ColorIndex = 2 Case 1: .Interior.ColorIndex = 6 Case 2: .Interior.ColorIndex = 3 Case 3: .Interior.ColorIndex = 43 Case 4: .Interior.ColorIndex = 41 Case Else End Select End With End If End Sub "Enyaw" wrote: I have the following code that changes the colour of a cell depending on the cells value. Here is my code. Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Range("G8:BC95")) Is Nothing Then With Target Select Case .Value Case 0: Target.Interior.ColorIndex = 2 Case 1: Target.Interior.ColorIndex = 6 Case 2: Target.Interior.ColorIndex = 3 Case 3: Target.Interior.ColorIndex = 43 Case 4: Target.Interior.ColorIndex = 41 End Select End With End If ws_exit: Application.EnableEvents = True End Sub The problem i have is the fields are already populated and i want to be able to loop through these values and update the cell colours. Can anyone help????? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
urgent!!!!!!
this may do what you want.
Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: With Application .EnableEvents = False .ScreenUpdating = False End With If Not Intersect(Target, Range("G8:BC95")) Is Nothing Then With Target For Each c In Range("G8:BC95") Select Case .Value Case 0 .Interior.ColorIndex = 2 Case 1 .Interior.ColorIndex = 6 Case 2 .Interior.ColorIndex = 3 Case 3 .Interior.ColorIndex = 43 Case 4 .Interior.ColorIndex = 41 End Select Next End With End If ws_exit: With Application .EnableEvents = True .ScreenUpdating = True End With End Sub -- jb "Enyaw" wrote: I have the following code that changes the colour of a cell depending on the cells value. Here is my code. Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Range("G8:BC95")) Is Nothing Then With Target Select Case .Value Case 0: Target.Interior.ColorIndex = 2 Case 1: Target.Interior.ColorIndex = 6 Case 2: Target.Interior.ColorIndex = 3 Case 3: Target.Interior.ColorIndex = 43 Case 4: Target.Interior.ColorIndex = 41 End Select End With End If ws_exit: Application.EnableEvents = True End Sub The problem i have is the fields are already populated and i want to be able to loop through these values and update the cell colours. Can anyone help????? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
urgent!!!!!!
I fully agree with your comments about the subject line description...Enyaw, take note.
As for the macro, we could take advantage of the numerical range and increment amounts that the OP posted and shorten it dramatically... Sub AmendColors() Dim C As Range, ColorValues As Variant ColorValues = Split("2 6 3 43 41") For Each C In Range("G8:BC95") If C.Value < "" Then C.Interior.ColorIndex = ColorValues(C.Value) Next End Sub Note that the Split function's first argument contains the list of ColorIndex values as a space delimited list. -- Rick (MVP - Excel) "Patrick Molloy" wrote in message ... The subject would be more useful if you put 'Cell color problem' URGENT gives us NO information and is unlikely to provoke a quick response. Change the procedure name to something like Sub AmendColors() then move it into a standard module...the changes are in the code below: OPTION EXPLICIT Sub AmendColors() dim cell as range for each cell in Range("G8:BC95").cells With cell Select Case .Value Case 0: .Interior.ColorIndex = 2 Case 1: .Interior.ColorIndex = 6 Case 2: .Interior.ColorIndex = 3 Case 3: .Interior.ColorIndex = 43 Case 4: .Interior.ColorIndex = 41 Case Else End Select End With End If End Sub "Enyaw" wrote: I have the following code that changes the colour of a cell depending on the cells value. Here is my code. Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Range("G8:BC95")) Is Nothing Then With Target Select Case .Value Case 0: Target.Interior.ColorIndex = 2 Case 1: Target.Interior.ColorIndex = 6 Case 2: Target.Interior.ColorIndex = 3 Case 3: Target.Interior.ColorIndex = 43 Case 4: Target.Interior.ColorIndex = 41 End Select End With End If ws_exit: Application.EnableEvents = True End Sub The problem i have is the fields are already populated and i want to be able to loop through these values and update the cell colours. Can anyone help????? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IME MODE FOR EXCEL 2007 (URGENT URGENT) | Excel Discussion (Misc queries) | |||
help - urgent | Excel Discussion (Misc queries) | |||
Urgent-Urgent VBA LOOP | Excel Discussion (Misc queries) | |||
Macro help urgent urgent | Excel Programming | |||
Macro help urgent urgent | Excel Programming |