Excel - how to check if Sheet1 includes only allowed characters defined in Sheet2

Question

I have an Excel file having 2 different sheets.

Sheet 1 includes raw data.

Name      Address                           Phone
Alex      1698 ABC street, L23 N22, ON      +1-547-222-4444
Mark      2212 XYZ street*, M23 N22, ON      +1-547-333-5555
Fred      1234 QWE street, L55 N62, ON      +1-547-666-7777
...

In sheet 2, I have a column indicating the allowed characters row by row:

AllowedCharacters
0
1
2
...
9
A
B
...
Z
a
b
...
z
-
/
\
&
,
+

What I need is to check if there is any row in Sheet1 including a non-allowed character (which is not in the list on Sheet2), and if so, I should highlight the cell by yellow.

So in the example data above, the Mark's address should be highlighted because it includes the character: *, which is not allowed.

What is the best way to do it?

Loop over all of the cell values, then loop over each character in the value and check whether it's in your list of allowed characters. — Tim Williams, Jun 06 '22 at 15:25
https://stackoverflow.com/questions/40311253/identify-special-characters — Tim Williams, Jun 06 '22 at 20:34

score 1 · Accepted Answer · answered Jun 07 '22 at 11:09

Maybe something like this ?

Sub test()
Dim rg As Range: Dim cell As Range
Dim crit As Range: Dim i As Long: Dim txt as String

With Sheets("Sheet1")
Set rg = .Range("A2", .Range("A" & Rows.Count).End(xlUp))
End With

Set crit = Sheets("Sheet2").Columns(1)

For Each cell In rg
txt = cell.Value & cell.Offset(0, 1).Value & cell.Offset(0, 2).Value
    For i = 1 To Len(txt)
        If Mid(txt, i, 1) <> " " Then
            If crit.Find("~" & Mid(txt, i, 1), lookat:=xlWhole) Is Nothing Then
                Range(cell, cell.Offset(0, 2)).Interior.Color = vbYellow
                Exit For
            End If
        End If
    Next i
Next cell

End Sub

First the sub make a range with data in Sheet1 column A into variable rg.
Then make the criteria range from Sheet2 column A into variable crit.

Then it loops the cell in rg
concatenate the text into txt variable
then it loops each character in txt variable
check if the looped char is found in crit
if not found then it highlight the cell and exit the loop for the char,
continue the next cell in rg.

score 0 · Answer 2 · answered Jun 06 '22 at 14:35

0

if you want to highlight by color you should try Conditional Formating, select cell you want to check > Home > Conditonal Formatting > Highlight Cell rule > Text that contain > set your rule and color. and then for another row you can copy the Formatting

answered Jun 06 '22 at 14:35

MD H

37
5

Tried like: =COUNTIF(A4:E4,"<>*'Sheet2'!B2:B60*") , didn't work.. if I put only one character instead of Sheet2'!B2:B60, it works. – Eray Balkanli Jun 06 '22 at 14:45
try this =COUNTIF(A4:E4,"<>*"&'Sheet2'!B2:B60&"*") – MD H Jun 06 '22 at 14:50
doesn't really help, sorry. Also COUNTIF is not taking the criteria as case sensitive as far as I understand. – Eray Balkanli Jun 06 '22 at 15:20

Excel - how to check if Sheet1 includes only allowed characters defined in Sheet2

2 Answers2