Conditional Formationg Color

S

Shpendi

Hi all
I have the following code to change the color of the cell.
What I need is for exmaple if cell B5 value is 0 to 5 then
Cell B6 Interior color turn into red.
Any ides
Thanks Shpendi

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

--------------------------------------------------
 
J

JulieD

Hi

try

Private Sub Worksheet_Change(ByVal Target As Range)

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

ws_exit:
Application.EnableEvents = True
End Sub

Cheers
JulieD
 
S

Shpendi

Thanks JulieD
I wasn't clear what exactly I needed.

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 sholud turn YELLOW
if the value of D20 is 24 Then D21 sholud turn GREEN
And so on until column N

Thanks
Shpendi


-----Original Message-----
Hi

try

Private Sub Worksheet_Change(ByVal Target As Range)

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top