Conditional Formating cocor VBA

[Shpendi]

Hi all
I have the following code to change the color of the cell(
the cell underneath from where the data is).
I have values from 0 to 100 in Range ("B20:N20").
e.g.
if the value of B20 is 4 then B21 should turn RED
if the value of C20 is 14 Then C21 should turn YELLOW
if the value of D20 is 15 to 24 Then D21 should turn GREEN
And so on form column 'B' to 'N'

____________________________CODE_______________________

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("B20:N20")) Is
Nothing Then
With Target
Select Case .Value
Case 0 To 5: .Interior.ColorIndex = 3
Case 6 To 20: .Interior.ColorIndex = 46
Case 21 To 30: .Interior.ColorIndex = 6
Case 31 To 40: .Interior.ColorIndex = 4
Case Is > 41: .Interior.ColorIndex = 10
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub
________________________end code_______________________

Any ides
Thanks
Shpendi

 

[Bob Phillips]

Assuming that the colours are correct

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("B20:N20")) Is Nothing Then
With Target
Select Case .Value
Case 0 To 5: .Offset(0,1).Interior.ColorIndex = 3
Case 6 To 20: .Offset(0,1).Interior.ColorIndex = 46
Case 21 To 30: .Offset(0,1).Interior.ColorIndex = 6
Case 31 To 40: .Offset(0,1).Interior.ColorIndex = 4
Case Is > 41: .Offset(0,1).Interior.ColorIndex = 10
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Shpendi]

Bob
it dosen't change the cell underneath it changers the cell
on the right.
I need it to change the cell underneath.
Thanks Shpendi

 

[JE McGimpsey]

Change the ".Offset(0, 1)"s from each line in Bob's macro to ".Offset(1,
0)".

 

[Guest]

Thankyou
So much both of you
Shpendi

-----Original Message-----
Change the ".Offset(0, 1)"s from each line in Bob's macro to ".Offset(1,
0)".


.