3

I have the following code that prompts for a string and then highlights all instances of it in the selected cells.

How can I modify it to prompt for and highlight multiple different words in one operation?

Sub HighlightStrings()
    Dim xHStr As String, xStrTmp As String
    Dim xHStrLen As Long, xCount As Long, I As Long
    Dim xCell As Range
    Dim xArr
    On Error Resume Next
    xHStr = Application.InputBox("What is the string to highlight:", "KuTools For Excel", , , , , , 2)
    If TypeName(xHStr) <> "String" Then Exit Sub
    Application.ScreenUpdating = False
        xHStrLen = Len(xHStr)
        For Each xCell In Selection
            xArr = Split(xCell.Value, xHStr)
            xCount = UBound(xArr)
            If xCount > 0 Then
                xStrTmp = ""
                For I = 0 To xCount - 1
                    xStrTmp = xStrTmp & xArr(I)
                    xCell.Characters(Len(xStrTmp) + 1, xHStrLen).Font.ColorIndex = 3
                    xStrTmp = xStrTmp & xHStr
                Next
            End If
        Next
    Application.ScreenUpdating = True
End Sub
  • It's working perfectly! The syntax is clear, the point is how to write it down, i don't know this language. – david Jul 4 '18 at 20:29
3

This modified code will allow you to enter multiple, space separated words, and all of them will be highlighted:

Option Explicit
'v0.1.1
Sub HighlightStrings()
    Dim xHStr As String, xStrTmp As String
    Dim xHStrLen As Long, xCount As Long, I As Long
    Dim xCell As Range
    Dim xArr
    On Error Resume Next
    xHStr = Application.InputBox("What are the words to highlight:", "Word Higlighter")
    If TypeName(xHStr) <> "String" Then Exit Sub
    Application.ScreenUpdating = False
        For Each xCell In Selection
            Dim varWord As Variant
            For Each varWord In Split(xHStr, Space$(1))
                xHStrLen = Len(varWord)
                xArr = Split(xCell.Value, varWord)
                xCount = UBound(xArr)
                If xCount > 0 Then
                    xStrTmp = ""
                    For I = 0 To xCount - 1
                        xStrTmp = xStrTmp & xArr(I)
                        xCell.Characters(Len(xStrTmp) + 1, xHStrLen).Font.ColorIndex = 3
                        xStrTmp = xStrTmp & varWord
                    Next
                End If
            Next varWord
        Next xCell
    Application.ScreenUpdating = True
End Sub

It uses the Split() function to separate out the entered words into an array, and then uses an extra loop to cycle through all the words for each cell in the selection.

Note that the code is case sensitive. It can be modified to become case insensitive by changing this statement

xArr = Split(xCell.Value, varWord)

to this

xArr = Split(UCase$(xCell.Value), UCase$(varWord))
  • Thanks a lot! I'm pretty new with this language. I try to learn step by step. – david Jul 4 '18 at 20:08
2

This modified version of the VBA code will Highlight Specific Words in Selected Range.

Create a Command Button & enter this Code.

Private Sub CommandButton1_Click()

Dim strSearch As String
Dim UserRange As Range
Dim arySearch As Variant
Dim searchRng As Range
Dim cel As Range
Dim i As Long, ii As Long

Set UserRange = Application.InputBox(Prompt:="Please Select Range", Title:="Range Select", Type:=8)

strSearch = InputBox("Please Enter Text To Highlight As A Comma Delimited List (Abc, Xyz) it's Case Sensative :", "Highlight Text")

If strSearch = "" Then Exit Sub
arySearch = Split(strSearch, ",")

For Each cel In UserRange

With cel

For ii = LBound(arySearch) To UBound(arySearch)

i = InStr(cel.Value, arySearch(ii))
If i > 0 Then

.Characters(i, Len(arySearch(ii))).Font.ColorIndex = 3
End If
Next ii
End With
Next cel
End Sub

How it works:

  • Click the Command button.
  • Respond the first Input Box by selecting the data range & finish with Ok.
  • Enter the Words separated by Comma (eg. Abc, Xyz) while Second Input Box appears and finish with Ok.

Note, Remember it's case sensitive so write Words Exactly as written in Cells.

  • Thanks a lot! I'm pretty new with this language. I try to learn step by step – david Jul 4 '18 at 20:10
  • Nice to hear from you @david, it's very simple and easier to use only you have to follow the instruction I've included, you get the desire result. I've already tested it. You may vote or accept the answer also. – Rajesh S Jul 5 '18 at 6:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.