CODEWITHSUNDEEP
excelvbasearchcellhighlight
IrisRose
IrisRose

Reputation: 61

Code to highlight a specific word in a specific column in Excel

I am searching for an excel code that searches for a specific keyword in a specific column and highlights it yellow; and is able to do this for multiple columns, each one with it's own distinct keyword.

Example:

Each time, the unique keyword is only highlighted within the specific column, even if it may occur in other columns, too.

The code would encompass 100 columns, from "Column A" to "Column CV", and allow the insert of a unique keyword for each column.

Is this possible?

Searching through the forums, I found codes that highlight specific words in excel, but none that narrow the search to a column and exclude the keyword from other columns.

This code, to find a word and color it red, has a similar core idea:

Sub colorText()

    Dim cl As Range
    Dim startPos As Integer
    Dim totalLen As Integer
    Dim searchText As String
    Dim endPos As Integer
    Dim testPos As Integer

 ' specify text to search.
 searchText = "river"

' loop trough all cells in selection/range
 For Each cl In Selection

  totalLen = Len(searchText)
  startPos = InStr(cl, searchText)
  testPos = 0

  Do While startPos > testPos
     With cl.Characters(startPos, totalLen).Font
      .FontStyle = "Bold"
      .ColorIndex = 3
    End With

    endPos = startPos + totalLen
    testPos = testPos + endPos
     startPos = InStr(testPos, cl, searchText, vbTextCompare)
  Loop

Next cl

End Sub

Only I would need a yellow highlight, not a red color. And I need it for excel 2016, and this code is for excel 2010.

Thank you.

Upvotes: 2

Views: 4484

Answers (1)

Tehscript
Tehscript

Reputation: 2556

Edit: You can either highlight a cell or change font color of a particular text in a cell. Excel does not have an option to highlight the background of particular text in a cell.

Since you want to see only the searched string get colored, I used Font.ColorIndex property and Red color instead of Yellow for ease of visibility.

I also declared an array so that you can enter your predefined 100 unique keywords as you like.

Let me know if it works for you:

Sub Search_by_Column()
Dim rng As Range
Dim i As Long
Dim oldrngrow As Long
Dim myValue As String
Dim arr() As Variant

arr = Array("river", "ocean", "sea") '..... keep going till 100 keywords

For i = 1 To UBound(arr) + 1
   myValue = arr(i - 1)
   If myValue = vbNullString Then
      End
   End If
   Set rng = Cells.Find(What:=myValue, After:=Cells(1, i), LookIn:=xlFormulas, LookAt _
      :=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
      False, SearchFormat:=False)
   If rng Is Nothing Then
      GoTo Skip
   End If

   oldrngrow = rng.Row
   Do While rng.Column = i
      rng.Characters(InStr(rng, myValue), Len(myValue)).Font.ColorIndex = 3
      Set rng = Cells.FindNext(After:=rng)
      If oldrngrow = rng.Row Then
        Exit Do
      End If
   Loop
Skip:
Next i
End Sub

Upvotes: 1

Related Questions