Validate a range to check if it is in specific format

[sn152]

Hi All,

I have a workbook where I have to validate the format of Column A. i.e., Column A should be in the below format:

1st four characters should be numbers and the next character should be "-" and then the rest should be text.

I have the below code. But it does not serve the purpose. Kindly help me here.

Sub Validate()

Dim LastRow As Long
Dim m As Variant

LastRow = Cells(Rows.Count, "A").End(xlUp).Row
m = Left(Range("A11"), 4)

If IsNumeric(m) Then
ActiveCell.Interior.ColorIndex = xlNone
Else
ActiveCell.Interior.ColorIndex = 3
End If

End Sub

 

[Deepak]

Something like this...

Function Validate_Cell(ByVal strInput As String) As Boolean
Dim b As Boolean, i As Integer
If Not Len(strInput) > 5 Then Exit Function
b = IsNumeric(Left(strInput, 4))
b = ("-" = Mid(strInput, 5, 1))
For i = 6 To Len(strInput)
    Select Case Asc(Mid(strInput, i, 1))
        Case 65 To 90, 97 To 122
        Case Else: b = False: Exit For
    End Select
Next
Validate_Cell = b
End Function

Sub Validate()

If Validate_Cell(Range("A11")) Then
    ActiveCell.Interior.ColorIndex = xlNone
Else
    ActiveCell.Interior.ColorIndex = 3
End If

End Sub

 

[sn152]

Thanks Deepak. This is checking only the cell A11. How to check all the cells after All in Column A? Also, after correcting a wrong entry if I run the code it should remove the color from the cells if the format is correct. Please help!

 

[jindon]

1st four characters should be numbers and the next character should be "-" and then the rest should be text.

Not really sure about "text" though.
Used Data Validation in column A.

Function CheckPattern(ByVal txt As String) As Boolean
    With CreateObject("VBScript.RegExp")
        .IgnoreCase = True
        .Pattern = "^\d{4}-[A-Z]+$"
        CheckPattern = .test(txt)
    End With
End Function

 

[sn152]

Thanks much Jindon. This is fine. Is there a way where if I enter something incorrectly in a cell it is showing a msg stating the value entered is no valid. Is it possible to customize or modify the msg?

 

[sn152]

Oh sorry i got this. Thank u so much Jindon.

 

[sn152]

Hi Jindon/Deepak,

How to change the format to show 4 numbers then a hyphen and then 1 character of text and then 9 numbers?

How do I change this "^\d{4}-[A-Z]+$" to show 4 numbers followed by a hyphen and then a letter and then 9 numbers?

Please help!

 

[jindon]

Hi Jindon/Deepak,

How to change the format to show 4 numbers then a hyphen and then 1 character of text and then 9 numbers?

How do I change this "^\d{4}-[A-Z]+$" to show 4 numbers followed by a hyphen and then a letter and then 9 numbers?

Please help!

Change to

        .Pattern = "^\d{4}-[A-Z]\d{9}$"

 

[sn152]

Thanks much..