Hi msog81,
There easiest way to add leading zeros to numbers is to convert it to text. Have a look at the code below:
Public TargetCell As Range
Sub Update_TargetCell()
TargetCell.NumberFormat = "@"
Select Case Len(TargetCell)
Case 1
TargetCell = "00" & TargetCell
Case 2
TargetCell = "0" & TargetCell
End Select
TargetCell.Errors(xlNumberAsText).Ignore = True
End Sub
This code will:
- set the target cell to text
- add zeros to the front of the number if required-
- ignore the annoying number stored as text error
To apply the solution to a range just run the following code:
Sub Update_Entire_Range()
For Each Target In Range("C2:C1000")
Set TargetCell = Target
Update_TargetCell
Next Target
End Sub
And to automatically run the code when a cell in the range is changed, just add this code in the respective worksheet object in VB editor:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C2:C1000")) Is Nothing Then
Set TargetCell = Target
Update_TargetCell
End If
End Sub
Let me know if that helped!
Cheers,
Mo
Bookmarks