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.

I haven't tried it myself yet but [this](https://scand.com/products/gspread/) apparently lets you use VBA in google sheets. — BerticusMaximus, Oct 09 '17 at 13:47
Possible duplicate of [Use VBA in Google Sheets to auto](https://stackoverflow.com/questions/32261465/use-vba-in-google-sheets-to-auto) — Luuklag, Oct 09 '17 at 14:01

score 2 · Accepted Answer · edited Jun 20 '20 at 09:12

2

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.

edited Jun 20 '20 at 09:12

Community

1
1

answered Oct 09 '17 at 15:11

TheMaster

45,448
6
62
85

This makes things a lot easier! Thank you very much for your kind help. Is there also an option to exclude the occurrence of doubled characters? Like allow the caracter "." but not ".."? – Castor Oct 10 '17 at 06:20
Thanks very much that works like a charm! In case i want to add more restrictions like not allowing "--" i just add more logical expressions right? The "." is not at the end cause I am setting up a sheet for URL verification :D – Castor Oct 10 '17 at 10:14
I’ve done so! Thanks again. Following question: If I want to set this up like a template for people to fill in a list of 1000 URLs or so, can I arrange this like an array or something? Because right now I have to set it up for 1000 rows and by default I, of course, get "FALSE" and "#VALUE" error as long as the accordant cell is empty and not filled in. This is just a usability/aesthetic factor of course. – Castor Oct 10 '17 at 11:56
Here I’ve set up a [Sample Google-Sheet](https://docs.google.com/spreadsheets/d/1SbnEMLNK2qyTJdir-zLSsROXwJLobGaVa_KJFML1PSE/edit?usp=sharing). As you can see I would prefer the sheet only to apply the rule whenever I enter URLs in column A. I have tried it like this: `ARRAYFORMULA(IF(A2:A,AND(NOT(REGEXMATCH(A2,"\-{2,}")),NOT(REGEXMATCH(A2,"\.{2,}")),REGEXMATCH(A2,"^[a-z0-9\-\/\:\.\=\?]+$")),""))` – Castor Oct 10 '17 at 12:41
thanks! I have changed that. However it does not seem to work. As I see I was thinking too twisted :D As it appeares I don’t even need my B column though. Things are getting more elegant with every edit :) – Castor Oct 10 '17 at 13:47

score -1 · Answer 2 · edited Oct 09 '17 at 15:58

-1

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.

edited Oct 09 '17 at 15:58

Rubén

34,714
9
70
166

answered Oct 09 '17 at 13:59

DataSmarter

197
2
14

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

2 Answers2

Conditional formatting >Custom formula:

Linked