Set background color based on cell value

Originally Posted by tgal

Hi, I have but my managers have the ability to **** up just about everything , that's why I want to use VBA.
Thanks,
Thomas

From this I take it he wants to cover just about every eventuality

I just don't see the point on limiting the code to working on one cell (at least without warning the user that they should only change one cell if they do change more) when a range can be handled quite easily.

I used to limit change events to working on one cell but personally think this is a better approach.

Dom

In that case I'd prefer:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Intersect(Target, Range("A1:A10")) Is Nothing Then Exit Sub
  For Each cl In Intersect(Target, Range("A1:A10"))
    With cl.Interior
      .ColorIndex = xlNone
      If cl > 0 And cl < 6 Then .ColorIndex = Choose(cl, 27, 3, 4, 32, 46)
      Sheets("Sheet2").Range(cl.Address).Interior.ColorIndex = .ColorIndex
    End With
  Next
End Sub