Hello,

I'm new to VB and am trying to come up with a macro that looks at the range P15:P100 and if it is numeric, then it formats the column one to the left with the "@#" format. Looking through other threads, I came up with the following, which works, but only when there is already a number in the offset column. If you enter a value after you run the macro, the formatting is no longer there. In short, I want it to look at the value when determining if it's a number, but format the entire cell, not just the value. Hope that's clear. Thanks in advance for any suggestions!

Sub Test()

For Each c In Range("P15:P100")
With c.Offset(0, -1)
If IsNumeric(.Value) Then
.Value = Format(.Value, """@"" ###,###,###,##0.00;[Red](###,###,###,##0.00)")
End If
End With
Next c
End Sub