Mad_Kitty
06-24-2008, 01:38 AM
I have been asked to update a spreadsheet that has been distributed around a number of people, who have filled in there own information. They may be using different versions of Excel but for my purposes I am using Excel 2003.
What they want is for the background colour of the cell to be changed depending on the value of the cell, for this purpose I have written the code and it works ok provided new numbers are entered. It does not work unless you enter a number and press enter for example if you copy and paste the cells all change to white.
Can any one point me in the right direction.
Thanks, the code I have is as follows.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer
On Error Resume Next
Application.DisplayAlerts = False
If Not Intersect(Target, Range("E17:P51")) Is Nothing Then
Select Case Target
Case 0 To 0
[White]
Case 1 To 75
icolor = 3
Case 76 To 95
icolor = 26
Case 96 To 100
icolor = 45
Case 101 To 102
icolor = 46
Case 103 To 105
icolor = 4
Case 106 To 1000
icolor = 50
Case Else
'Whatever
End Select
Target.Interior.ColorIndex = icolor
End If
End Sub
What they want is for the background colour of the cell to be changed depending on the value of the cell, for this purpose I have written the code and it works ok provided new numbers are entered. It does not work unless you enter a number and press enter for example if you copy and paste the cells all change to white.
Can any one point me in the right direction.
Thanks, the code I have is as follows.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer
On Error Resume Next
Application.DisplayAlerts = False
If Not Intersect(Target, Range("E17:P51")) Is Nothing Then
Select Case Target
Case 0 To 0
[White]
Case 1 To 75
icolor = 3
Case 76 To 95
icolor = 26
Case 96 To 100
icolor = 45
Case 101 To 102
icolor = 46
Case 103 To 105
icolor = 4
Case 106 To 1000
icolor = 50
Case Else
'Whatever
End Select
Target.Interior.ColorIndex = icolor
End If
End Sub