The sheet sub below works fine if I input the values (1-6) manually into
V2:V250. How can it be amended to work if V2:V250 contains formulas
returning the values 1-6 instead? Thanks

Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer
If Not Intersect(Target, Range("V2:V250")) Is Nothing Then
Select Case Target
Case 1
icolor = 10
fcolor = 2
Case 2
icolor = 50
fcolor = 2
Case 3
icolor = 4
fcolor = 1
Case 4
icolor = 35
fcolor = 1
Case 5
icolor = 44
fcolor = 1
Case 6
icolor = 45
fcolor = 2
Case Else
End Select
With Target
.Offset(0, -21).Resize(1, 21).Interior.ColorIndex = icolor
.Offset(0, -21).Resize(1, 21).Font.ColorIndex = fcolor
End With
End If
End Sub

Max,

Try this, it's got a bit messy but it works:-

Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer
Set myrange = Range("V2:V250")
For Each c In myrange
Select Case c.Value
Case 1
icolor = 10
fcolor = 2
With c
.Offset(0, -21).Resize(1, 21).Interior.ColorIndex = icolor
.Offset(0, -21).Resize(1, 21).Font.ColorIndex = fcolor
End With
Case 2
icolor = 50
fcolor = 2
With c
.Offset(0, -21).Resize(1, 21).Interior.ColorIndex = icolor
.Offset(0, -21).Resize(1, 21).Font.ColorIndex = fcolor
End With
Case 3
icolor = 4
fcolor = 1
With c
.Offset(0, -21).Resize(1, 21).Interior.ColorIndex = icolor
.Offset(0, -21).Resize(1, 21).Font.ColorIndex = fcolor
End With
Case 4
icolor = 35
fcolor = 1
With c
.Offset(0, -21).Resize(1, 21).Interior.ColorIndex = icolor
.Offset(0, -21).Resize(1, 21).Font.ColorIndex = fcolor
End With
Case 5
icolor = 44
fcolor = 1
With c
.Offset(0, -21).Resize(1, 21).Interior.ColorIndex = icolor
.Offset(0, -21).Resize(1, 21).Font.ColorIndex = fcolor
End With
Case 6
icolor = 45
fcolor = 2
With c
.Offset(0, -21).Resize(1, 21).Interior.ColorIndex = icolor
.Offset(0, -21).Resize(1, 21).Font.ColorIndex = fcolor
End With
Case Else
With c
.Offset(0, -21).Resize(1, 21).Interior.ColorIndex = xlNone
.Offset(0, -21).Resize(1, 21).Font.ColorIndex = 0
End With
End Select
Next
End Sub

Mike


"Max" wrote:

> The sheet sub below works fine if I input the values (1-6) manually into
> V2:V250. How can it be amended to work if V2:V250 contains formulas
> returning the values 1-6 instead? Thanks
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Dim icolor As Integer
> If Not Intersect(Target, Range("V2:V250")) Is Nothing Then
> Select Case Target
> Case 1
> icolor = 10
> fcolor = 2
> Case 2
> icolor = 50
> fcolor = 2
> Case 3
> icolor = 4
> fcolor = 1
> Case 4
> icolor = 35
> fcolor = 1
> Case 5
> icolor = 44
> fcolor = 1
> Case 6
> icolor = 45
> fcolor = 2
> Case Else
> End Select
> With Target
> .Offset(0, -21).Resize(1, 21).Interior.ColorIndex = icolor
> .Offset(0, -21).Resize(1, 21).Font.ColorIndex = fcolor
> End With
> End If
> End Sub
>
>
>

Many thanks, Mike. It works fine.