Amend sheet sub to work if input range contains formulas

[Max]

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

 

[Mike H]

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]

Many thanks, Mike. It works fine.