Sub findthefour()



Dim i As Integer, j As Integer, srange As String
Dim sCell As Range

'i = 0
For Each sCell In Selection
If Left(Right(sCell.Value, 4), 1) = "4" Then

sCell.Interior.ColorIndex = 6
sCell.Offset(0, -1).Value = 1


'arr(i) = sCell.Address
'i = i + 1
Else
sCell.Offset(0, -1).Value = 0

End If
Next

End Sub

this will do what i said
just insert a col before a and highlight the number col