vba regEx multiple patterns for different columns

Question

I need to perform two different validation in two columns. In column "C" I need to check special characters and in column "D" I need to check if there are numbers.

Is there anyway I can achieve this using regEx in VBA?

Thanks for your help in advance

Dim strPattern As String: strPattern = "[^a-z]"
Dim regEx As Object
Set regEx = CreateObject("VBScript.RegExp")
regEx.Global = True
regEx.IgnoreCase = True
regEx.Pattern = strPattern

For Each cell In ActiveSheet.Range("C:C") ' Define your own range here
    If strPattern <> "" Then              ' If the cell is not empty
        If regEx.Test(cell.Value) Then    ' Check if there is a match
            cell.Interior.ColorIndex = 6  ' If yes, change the background color
        End If
    End If
Next

You can use `WildCards` with [Like Operator](https://learn.microsoft.com/en-us/dotnet/visual-basic/language-reference/operators/like-operator) — danieltakeshi, Oct 10 '17 at 13:29
`Is there anyway I can achieve this using regEx in VBA?` - Yes. For more help, please read [how to ask](http://stackoverflow.com/help/how-to-ask) — Scott Holtzman, Oct 10 '17 at 13:29
Dim strPattern As String: strPattern = "[^a-z]" Dim regEx As Object Set regEx = CreateObject("VBScript.RegExp") regEx.Global = True regEx.IgnoreCase = True regEx.Pattern = strPattern For Each cell In ActiveSheet.Range("C:C") ' Define your own range here If strPattern <> "" Then ' If the cell is not empty If regEx.Test(cell.Value) Then ' Check if there is a match cell.Interior.ColorIndex = 6 ' If yes, change the background color End If End If Next — Rick, Oct 10 '17 at 13:48
this is how my current code looks, took from this website only. now i need to apply on D column to verify numbers in the same script validation. — Rick, Oct 10 '17 at 13:50
@Rick If you use a Negative Match `[^A-Za-z0-9]`, it can detect special characters, however, it will detect `´~çÇàáé, can you specify the special characters? Or it must be [all of them](https://stackoverflow.com/a/18058074/7690982)? — danieltakeshi, Oct 10 '17 at 14:09
It should detect any character other than alphabets in column “C” and column “D” anything other than numbers. — Rick, Oct 10 '17 at 15:11

danieltakeshi · Accepted Answer · 2017-10-10T19:29:00.163

Code

Using your code, this is the result:

Dim strPattern As String
Dim regEx As Object
Dim ncellc As Long, ncelld As Long, i As Long
Dim rng As Range

Set regEx = CreateObject("VBScript.RegExp")
regEx.Global = True
regEx.IgnoreCase = True

ncellc = ActiveSheet.Cells(ActiveSheet.Rows.Count, "C").End(xlUp).Row
ncelld = ActiveSheet.Cells(ActiveSheet.Rows.Count, "D").End(xlUp).Row

For i = 1 To 2
    If i = 1 Then
        strPattern = "[^a-z]"
        Set rng = Range("C1:C" & ncellc)
    ElseIf i = 2 Then
        strPattern = "[^\d]"
        Set rng = Range("D1:D" & ncelld)
    End If
    regEx.Pattern = strPattern

    For Each cell In rng                     ' Define your own range here
        If strPattern <> "" And cell <> "" Then ' If the cell is not empty and there is pattern
            If regEx.test(cell.Value) Then   ' Check if there is a match
                cell.Interior.ColorIndex = 3 ' If yes, change the background color
            Else
                cell.Interior.ColorIndex = 4
            End If
        End If
    Next
Next i

Regex

That used simple Negative Match Regex code.

[^a-z] To reject alphabet letters and [^\d] to reject numbers

Thanks much. It worked, I need to alter this code little bit to suite my condition. If I need any help in that I will check with you. — Rick, Oct 11 '17 at 01:35

vba regEx multiple patterns for different columns

1 Answers1

Code

Regex