How to use VBA in Google Sheets to highlight cells with special characters and uppercase letters?

Question

I managed to get a VBA-snippet working in Microsoft Excel that highlights all Cells in Range C1 to E10000 which contain anything else than lowercase a–z, numbers and hyphens.

The code looks like this:

Option Explicit
Sub SpecialChars()
    Dim RangeToCheck As Range, c As Range

Set RangeToCheck = Range("C1:E10000")
For Each c In RangeToCheck
    If Len(c.Text) > 0 Then
        If c.Text Like "*[!.a-z0-9\-]*" Then
            c.Interior.Color = vbRed
        Else: c.Interior.Color = vbYellow
        End If
    End If
Next c
End Sub

What is the proper way to transfer this to Google Sheets? I think the problem might be, that Google uses JavaScript while Excel uses something else.

Still I wanted to give it a shot here.

Answer 1

I don't think you need scripts. You could use conditional formatting. It seems the only reason you're using VBA is because you need REGEX, which Microsoft excel doesn't support except through VBA. Google Sheets however has REGEX support inbuilt.

... highlights all Cells in Range C1 to E10000 which contain anything else than lowercase a–z, numbers and hyphens....

Conditional formatting >Custom formula:

=REGEXMATCH(C1,"^[a-z0-9\-]+$")

This will be used to match

• Lowercase
• Numbers
• Hyphen (-)

Highlight yellow
Apply to range: C1:E10000

Note: Spaces are not matched. So, If you have spaces, It will not match.

Answer 2

If you work in Google Apps, you should better do it via the GAS (Google Apps Script). That is the JavaScript based Google's equivalent to VBA.