In one column (F), I have data like 0, 10, 30, 60, 90 and 100. I need to
change the background colour of another column (B) depending on what the
entry is in column F.

e.g. f2 = 0 or 100 then a2 = red, 30 = blue, 60 = orange, 90 = green, 10 =
no change

One way:

Assuming your column Fvalues are entered manually, put this in your
worksheet code module (right-click the worksheet tab and choose View
Code):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rArea As Range
Dim rCell As Range
Dim nColor As Long
Set Target = Intersect(Target, Range("F:F"))
If Not Target Is Nothing Then
For Each rArea In Target
For Each rCell In rArea
Select Case rCell.Value
Case 0, 100
nColor = RGB(255, 0, 0)
Case 30
nColor = RGB(0, 0, 255)
Case 60
nColor = RGB(255, 102, 0)
Case 90
nColor = RGB(0, 255, 0)
Case Else
nColor = -1
End Select
If Not nColor = -1 Then _
rCell.Offset(0, -5).Interior.Color = nColor
Next rCell
Next rArea
End If
End Sub


In article ,
harwookf wrote:

In one column (F), I have data like 0, 10, 30, 60, 90 and 100. I need to
change the background colour of another column (B) depending on what the
entry is in column F.

e.g. f2 = 0 or 100 then a2 = red, 30 = blue, 60 = orange, 90 = green, 10 =
no change

Thanks for the speedy response. I think I am missing something though.
I have copied and pasted this information into the worksheet code module,
but it doesn't change the colour of anything.

Do I need to do something else?

Regards

harwookf

"JE McGimpsey" wrote:

One way:

Assuming your column Fvalues are entered manually, put this in your
worksheet code module (right-click the worksheet tab and choose View
Code):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rArea As Range
Dim rCell As Range
Dim nColor As Long
Set Target = Intersect(Target, Range("F:F"))
If Not Target Is Nothing Then
For Each rArea In Target
For Each rCell In rArea
Select Case rCell.Value
Case 0, 100
nColor = RGB(255, 0, 0)
Case 30
nColor = RGB(0, 0, 255)
Case 60
nColor = RGB(255, 102, 0)
Case 90
nColor = RGB(0, 255, 0)
Case Else
nColor = -1
End Select
If Not nColor = -1 Then _
rCell.Offset(0, -5).Interior.Color = nColor
Next rCell
Next rArea
End If
End Sub


In article ,
harwookf wrote:

In one column (F), I have data like 0, 10, 30, 60, 90 and 100. I need to
change the background colour of another column (B) depending on what the
entry is in column F.

e.g. f2 = 0 or 100 then a2 = red, 30 = blue, 60 = orange, 90 = green, 10 =
no change

Evidently...

Are you changing the values in column F?

IF you set a breakpoint in the code, does it get executed?

In article ,
harwookf wrote:

Thanks for the speedy response. I think I am missing something though.
I have copied and pasted this information into the worksheet code module,
but it doesn't change the colour of anything.

Do I need to do something else?

Regards

harwookf

"JE McGimpsey" wrote:

One way:

Assuming your column Fvalues are entered manually, put this in your
worksheet code module (right-click the worksheet tab and choose View
Code):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rArea As Range
Dim rCell As Range
Dim nColor As Long
Set Target = Intersect(Target, Range("F:F"))
If Not Target Is Nothing Then
For Each rArea In Target
For Each rCell In rArea
Select Case rCell.Value
Case 0, 100
nColor = RGB(255, 0, 0)
Case 30
nColor = RGB(0, 0, 255)
Case 60
nColor = RGB(255, 102, 0)
Case 90
nColor = RGB(0, 255, 0)
Case Else
nColor = -1
End Select
If Not nColor = -1 Then _
rCell.Offset(0, -5).Interior.Color = nColor
Next rCell
Next rArea
End If
End Sub


In article ,
harwookf wrote:

In one column (F), I have data like 0, 10, 30, 60, 90 and 100. I need to
change the background colour of another column (B) depending on what the
entry is in column F.

e.g. f2 = 0 or 100 then a2 = red, 30 = blue, 60 = orange, 90 = green, 10
=
no change

Oops, I'd changed the column. Silly billy, or words to that effect.
Anyway, now that I have the correct column, it comes up with an error and is
highlighting the line:-
rCell.Offset(0, -5).Interior.Color = nColor



"JE McGimpsey" wrote:

Evidently...

Are you changing the values in column F?

IF you set a breakpoint in the code, does it get executed?

In article ,
harwookf wrote:

Thanks for the speedy response. I think I am missing something though.
I have copied and pasted this information into the worksheet code module,
but it doesn't change the colour of anything.

Do I need to do something else?

Regards

harwookf

"JE McGimpsey" wrote:

One way:

Assuming your column Fvalues are entered manually, put this in your
worksheet code module (right-click the worksheet tab and choose View
Code):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rArea As Range
Dim rCell As Range
Dim nColor As Long
Set Target = Intersect(Target, Range("F:F"))
If Not Target Is Nothing Then
For Each rArea In Target
For Each rCell In rArea
Select Case rCell.Value
Case 0, 100
nColor = RGB(255, 0, 0)
Case 30
nColor = RGB(0, 0, 255)
Case 60
nColor = RGB(255, 102, 0)
Case 90
nColor = RGB(0, 255, 0)
Case Else
nColor = -1
End Select
If Not nColor = -1 Then _
rCell.Offset(0, -5).Interior.Color = nColor
Next rCell
Next rArea
End If
End Sub


In article ,
harwookf wrote:

In one column (F), I have data like 0, 10, 30, 60, 90 and 100. I need to
change the background colour of another column (B) depending on what the
entry is in column F.

e.g. f2 = 0 or 100 then a2 = red, 30 = blue, 60 = orange, 90 = green, 10
=
no change

You probably need to change the offset, too.

But you didn't give any info about either what error, or what columns
you changed to, so it's pretty hard to guess what change you need...

In article ,
harwookf wrote:

Oops, I'd changed the column. Silly billy, or words to that effect.
Anyway, now that I have the correct column, it comes up with an error and is
highlighting the line:-
rCell.Offset(0, -5).Interior.Color = nColor