Highlight cells which does not contain certain set of letter and numbers

[Niranjanrajrishi]

Hello.,

Can anyone help me to build a macro to to identify cell and raw which has values others than the set of letters, numbers and special characters allowed? I have a huge of values and need to identify the rows and cells which has values others than the set values (letters, numbers, special characters). I have listed out those values which need to use in the macro. Let's say, A, B, C, 1,2,3 are my set values and need to identify the raw and cells which has value other than these values.

 

[NARAYANK991]

Hi ,

Please upload a sample workbook , which has the following :

1. The complete list of permitted characters / values

2. A sample set of data which caters to all possible variations ; it should reflect the data in your working file as closely as possible. The more rows of data that are available , the more thorough the testing can be.

3. The layout of data and the permitted character list should be identical to what is present in your working file.

Narayan

 

[Niranjanrajrishi]

Please find the attached file with the specification. The sheet called "Allowed Characters" has the letters, numbers and special characters to be checked. If any cell has other than these values then it should highlight and put a comment "change" in the last column.

 

[p45cal]

Select the cells you want to check, then run this macro Put thecode in a standard code-module, not a sheet's code module; it will then work on the ACTIVE SHEET. It will colour the cells red and add 'Change' to the rows in column BD.

Sub blah()
allowed = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789!#$%&?()+,-./;<>?@[]`{| }"""
For Each cll In Selection.Cells
  cllval = cll.Value
  For i = 1 To Len(cllval)
    If InStr(1, allowed, Mid(cllval, i, 1), vbTextCompare) = 0 Then
      cll.Interior.Color = rgbRed
      Cells(cll.Row, "BD").Value = "Change"
      Exit For
    End If
  Next i
Next cll
End Sub

 

[Niranjanrajrishi]

Wow! my excitement continuous... it worked like a charm! You made it happen. Thank you so much.