urgent!!!!!!

[Enyaw]

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?????

 

[Patrick Molloy]

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

 

[john]

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

 

[Rick Rothstein]

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.