Conditional formating based on Value = Range

[dickie]

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

[Kenneth Hobs]

Welcome to the forum!

Please click the VBA icon to insert code tags in paste your code between them.

I am not sure why you want want to update all those cells. I would recommend that you do the intersect for the H column cells and when they change, then update the adjacent G column cell.

Right click your sheet tab, View Code, and paste:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim NewColor As Integer, cell As Range
Range("G35:G40").Value = Range("H35:H40").Value

If Target.Cells.Count = 1 Then Exit Sub
Application.EnableEvents = False
For Each cell In Target
NewColor = xlNone
Select Case cell.Text
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
cell.Interior.ColorIndex = NewColor
Next cell
Application.EnableEvents = True
End Sub

[dickie]

Hi Kenneth. Thanks for your speedy reply and my appologies for not displaying the VBA properly. Im probably demonstrating what a luddite I am here but I have tried what you suggest and Im getting a 'Run Time Error 28' Out of stack space.......:(

Any Ideas?

[Kenneth Hobs]

You may have too much other code in that workbook. If this kind of thing is done like you first posted, Excel will likely close and may not save. Always work on a backup copy when testing code.

To see if code works, try it in a new workbook.

That code worked in my 2010.

[dickie]

I’m afraid this was a new spread sheet with no other code. I’m working with2007 but I can’t imagine that that’s the issue. Conscious I shouldn’t take upany more of your time Kenneth. But thanks for your help anyway.

Cheers

Dickie