Forum Discussion
Clara2475
Mar 15, 2023Copper Contributor
Custom cell format
Hi, I'm looking to create a format for my cells so they include text, numbers and place holders that displays insignificant zeros (quite similar to a postal code). Not all inputs will have 5 digi...
JKPieterse
Mar 15, 2023Silver Contributor
Clara2475 As far as I know, you cannot achieve this with a conditional format.
This VBA code in the code module belonging to the worksheet should do the trick:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim v As Variant
If Intersect(Target, Range("B2:B10")) Is Nothing Then Exit Sub
If Target.Cells.Count = 1 Then
If Target.Value Like "??##-#*" Then
v = Split(Target.Value, "-")
Application.EnableEvents = False
Target.Value = v(0) & "-" & v(1) & String(4 - Len(v(1)), "0")
Application.EnableEvents = True
Else
MsgBox "Invalid format, please enter two characters, two digits a dash and one to four digits:" & vbNewLine & _
"AA11-0000"
End If
End If
End Sub