1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

Discussion in 'Ask an Excel Question' started by Niranjanrajrishi, Dec 6, 2017.

  1. Niranjanrajrishi

    Niranjanrajrishi Member


    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.
  2. NARAYANK991

    NARAYANK991 Excel Ninja

    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.

  3. Niranjanrajrishi

    Niranjanrajrishi Member

    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.

    Attached Files:

  4. p45cal

    p45cal Well-Known Member

    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.
    Code (vb):
    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
  5. Niranjanrajrishi

    Niranjanrajrishi Member

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

Share This Page