Find all cells that do not contain specific text

I have a column of data which I need to get the cell address of if it does not contain specific characters in the first 4 characters in the contents.

For example I want to find all cells that does not contain one of the following as the first four characters: {AAA, {DDD, {SSS, {XYZ

Sample data could look like this:

These are ok since they match the criteria - I do not want to find them

I want to find these cells since the first 4 characters do not match any of the values I am looking for

When I find those cells I want to get the cell information (row) as well as color the cell with red.

I know I can loop through each cell in the column, however, that is pretty slow.

In this example (
Range("A1:D500").SpecialCells(xlCellTypeBlanks) = "Blank"

Open in new window

) I can find blank cells very quickly. However I do not know how to translate that code into something that will achieve the requirement I have.

There may be no other way than to loop through each cell - if so that is what I will do.
Who is Participating?
Rory ArchibaldConnect With a Mentor Commented:
Helps if I add the code...
Sub highlights()
   Dim lngLastRow        As Long
   Dim i                 As Long
   Dim strRng            As String
   Dim varData
   Dim strColumn         As String

   ' change to match your column
   strColumn = "A"

   lngLastRow = Cells(Rows.Count, strColumn).End(xlUp).Row

   varData = Range(Cells(1, strColumn), Cells(lngLastRow, strColumn))

   Application.ScreenUpdating = False

   For i = LBound(varData, 1) To UBound(varData, 1)     'change 4 to whatever you want for start row
      'In the next line, change >3 to whatever you like, such as ="Ckid" or
      'whatever string you want, or to =vbnullstring if you want to count/mark blanks
      Select Case Left$(UCase$(varData(i, 1)), 4)
         Case "{AAA", "{DDD", "{SSS", "{XYZ"
            ' do nothing
         Case Else
            strRng = strRng & "," & strColumn & i
            If Len(strRng) > 245 Then
               Range(Right(strRng, Len(strRng) - 1)).Interior.Color = vbRed
               strRng = vbNullString
            End If
      End Select
   Next i
   Range(Right(strRng, Len(strRng) - 1)).Interior.Color = vbRed
   Application.ScreenUpdating = True
End Sub

Open in new window

Rory ArchibaldCommented:
You could try something like this. Only takes a few seconds even on an entire column of 1m+ rows.
ckelsoeAuthor Commented:
Your code worked perfectly with some adaptions to my sheet. I did not think of using the select case to help solve this problem. Thanks very much.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.