Programming in excel

[Sandwiches2]

Can anyone help me rework this code. What I need is the code to call four
columns. First is a name column. Then a space then 3 numbers separated by
slashes each with a different rule for coloring.

So example let’s say name is Smith

I want Smith 32/15/3.4

And the column where 32 comes from let’s say has parameters 20+ is green,
15-19 is black, 10-14 is red and below 10 is plum and bold. Second column
number would be similar except that the parameters would change. So 9-12
would be green, 13-14 black, 15-17 red and 18+ plum and bold

My feeling is that I would need three if statements for the colors, but I
can’t seem to append the three numbers to a single column. Please help!

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const sSep As String = "/"
Dim nLen(1 To 3) As Long
Dim nColorIndex As Long
Dim nPos As Long
Dim i As Long
Dim dValue(1 To 3) As Double
Dim sTemp As String
Dim sVal As String
Dim bBold As Boolean
With Range("A1:C1")
For i = 1 To 3
sVal = .Item(i).Text
nLen(i) = Len(sVal)
If IsNumeric(sVal) Then dValue(i) = CDbl(sVal)
sTemp = sTemp & sSep & sVal
Next i
End With
On Error GoTo ErrHandler
Application.EnableEvents = False
With Range("J10") 'Destination Cell
.ClearFormats
.NumberFormat = "@"
.Value = Mid(sTemp, 2)
nPos = 1
For i = 1 To 3
If nLen(i) > 0 Then
Select Case dValue(i)
Case Is < 3
nColorIndex = 5 'default blue
bBold = True
Case Is >= 15
nColorIndex = 10 'default green
bBold = False
Case Else
nColorIndex = xlColorIndexAutomatic
bBold = False
End Select
With .Characters(nPos, nLen(i)).Font
.Bold = bBold
.ColorIndex = nColorIndex
End With
End If
nPos = nPos + nLen(i) + Len(sSep)
Next i
End With
ErrHandler:
Application.EnableEvents = True
End Sub