Hi All,

I'm getting a "ByRef argument type mismatch" error in my code. I don't quite understand how this is happening, since I thought the Regular Expression will still work with a string. Below are the relevant snippets of code.


Sub BirthdayCheck()

Dim FlexValue as Variant
Dim ColumnNumber as Integer

'Get's column number for the "Birthdate" column
ColumnNumber = WorksheetFunction.match("Birthdate",Sheet1.Range("A1:Z1"),0)

'Iterates through 100 rows
For i = 1 to 100 

With Sheet1.Cells(i,ColumnNumber)
'Assigns value to FlexValue for that iteration's row
FlexValue = Sheet1.cells(i,ColumnNumber)

'Checks date format YYYY-MM-DD, ByRef error on 'FlexValue'
If Not (RegExDate(FlexValue)) Then
     .Interior.ColorIndex = 3
End If

Next I

End Sub

'This worked in my test module
Private Function RegExDate(s As String) As Boolean

    Dim re, match
    Set re = CreateObject("vbscript.regexp")
    re.Pattern = "(19|20)[0-9]{2}[- -.](0[1-9]|1[012])[- -.](0[1-9]|[12][0-9]|3[01])"
    re.Global = True
    
    For Each match In re.Execute(s)
        RegExDate = True
        Exit For
    Next
    Set re = Nothing

End Function
Wondering if someone can help me out. This place has always been great.

Thanks!
1980