Change colour of cells depending on entry in one cell

[harwookf]

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

 

[JE McGimpsey]

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

 

[harwookf]

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]

Evidently...

Are you changing the values in column F?

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

 

[harwookf]

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]

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

 

[harwookf]

I'm now using Column F for the 0, 10, etc., but I need to highlighy the cells
in Column A (not B).
When I change a value, it shows a run-time error '1004': Application-defined
or object-defined error, then it gives me the option to End or Debug. If I
Debug, it has the following line highlighted.

rCell.Offset(0, -5).Interior.Color = nColor

Does this help?

 

[harwookf]

I'm now using Column F for the 0, 10, etc., but I need to highlighy the cells
in Column A (not B).
When I change a value, it shows a run-time error '1004': Application-defined
or object-defined error, then it gives me the option to End or Debug. If I
Debug, it has the following line highlighted.

rCell.Offset(0, -5).Interior.Color = nColor

I copied and pasted it so it is exactly as given, though I have now changed
the F:F to E:E, but it still doesn't work.

Can you help me anymore?

 

[harwookf]

I have changed the offset, so it now works, but can it be modified so that if
the numbers are anything other than 0, 30, 60, 90 or 100, it will default to
the standard no fill?

Also, will this still work if the spreadsheet is shared?

Many thanks

 

[JE McGimpsey]

One way:

Change:

If Not nColor = -1 Then _
rCell.Offset(0, -5).Interior.Color = nColor

To

If Not nColor = -1 Then
rCell.Offset(0, -5).Interior.Color = nColor
Else
rCell.Offset(0, -5).Interior.ColorIndex = _
xlColorIndexNone
End If