dickie
09-28-2011, 08:10 AM
Hello All
Please can I just say that I am a complete novice when it comes to VBA so please bare with me if I am making any fundermental and ultimately stupid mistakes:think: Any help / guidance you can offer would be very much appreciated!
This issues has come about becuase (as far as I can make out?) you cant run VBA on an a cell IF statement. I though I had solved this with the following - but this returns a 'Run Time Error on Case 0. Thanks in advance for your guidance. regards
PrivateSub Worksheet_Change(ByVal Target As Range)
Range("G35:G40").Value = Range("H35:H40").Value
Set I = Intersect(Target, Range("G35:G40"))
If Not I Is Nothing Then
Select Case Target
Case 0: NewColor = 45 ' light orange 0
Case 1: NewColor = 36 ' light yellow 1
Case 2: NewColor = 43 ' green 2
Case 3: NewColor = 36 ' light yellow 3
Case 4: NewColor = 45 ' light orange 4
Case 5: NewColor = 3 ' red 5 and greater
Case 6: NewColor = 3 ' red 5 and greater
Case 7: NewColor = 3 ' red 5 and greater
End Select
Target.Interior.ColorIndex = NewColor
End If
End Sub
Please can I just say that I am a complete novice when it comes to VBA so please bare with me if I am making any fundermental and ultimately stupid mistakes:think: Any help / guidance you can offer would be very much appreciated!
This issues has come about becuase (as far as I can make out?) you cant run VBA on an a cell IF statement. I though I had solved this with the following - but this returns a 'Run Time Error on Case 0. Thanks in advance for your guidance. regards
PrivateSub Worksheet_Change(ByVal Target As Range)
Range("G35:G40").Value = Range("H35:H40").Value
Set I = Intersect(Target, Range("G35:G40"))
If Not I Is Nothing Then
Select Case Target
Case 0: NewColor = 45 ' light orange 0
Case 1: NewColor = 36 ' light yellow 1
Case 2: NewColor = 43 ' green 2
Case 3: NewColor = 36 ' light yellow 3
Case 4: NewColor = 45 ' light orange 4
Case 5: NewColor = 3 ' red 5 and greater
Case 6: NewColor = 3 ' red 5 and greater
Case 7: NewColor = 3 ' red 5 and greater
End Select
Target.Interior.ColorIndex = NewColor
End If
End Sub