Highlight Cells Based on Text with Excel VBA

Objective:

The goal is to create a VBA macro that highlights cells containing specific text in a specified range of cells.

Code:

Sub HighlightCellsBasedOnText()
    ' Declare variables
    Dim ws As Worksheet
    Dim rng As Range
    Dim cell As Range
    Dim searchText As String
    Dim highlightColor As Long
    ' Set the worksheet where you want to perform the operation
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Replace with your sheet name    
    ' Set the range where you want to search for the text (e.g., A1 to D10)
    Set rng = ws.Range("A1:D10") ' Replace with your desired range    
    ' Define the text to search for
    searchText = "HighlightMe" ' Replace with the text you're searching for    
    ' Define the color to highlight cells with (e.g., Yellow)
    highlightColor = RGB(255, 255, 0) ' Yellow color
    ' Loop through each cell in the specified range
    For Each cell In rng
        ' Check if the cell contains the specific text
        If InStr(1, cell.Value, searchText, vbTextCompare) > 0 Then
            ' If the text is found, highlight the cell with the specified color
            cell.Interior.Color = highlightColor
        Else
            ' If the text is not found, remove any existing color formatting
            cell.Interior.ColorIndex = xlNone
        End If
    Next cell    
    ' Optional: Provide a message once the process is complete
    MsgBox "Cells highlighted based on the search text.", vbInformation
End Sub

Explanation of the Code:

1. Declaring Variables:
   • ws: This is a reference to the worksheet where we will search for text. You can change "Sheet1" to any other worksheet name.
   • rng: The range where we will look for text. In this example, it’s the range A1:D10, but you can adjust it to any range you need.
   • cell: This variable will represent each individual cell within the range.
   • searchText: This holds the specific text we want to search for inside the cells. Change "HighlightMe" to whatever text you’re looking for.
   • highlightColor: This defines the color to apply for highlighting. In this case, RGB(255, 255, 0) corresponds to a yellow color. You can adjust this value to change the highlight color.
2. Setting the Worksheet and Range:
   • Set ws = ThisWorkbook.Sheets("Sheet1"): This tells VBA to use Sheet1 of the workbook where the code is running. Replace "Sheet1" with the name of the sheet you want to work with.
   • Set rng = ws.Range("A1:D10"): This sets the range in which we will search for the text. You can modify A1:D10 to suit your needs. For example, you can use "A1:A100" to search the entire column A, or "B2:F20" for a different range.
3. Searching for Text and Highlighting:
   • The For Each cell In rng loop iterates through each cell in the specified range.
   • Inside the loop, InStr(1, cell.Value, searchText, vbTextCompare) is used to check if searchText is present within the cell’s value.
     • InStr returns the position of the first occurrence of searchText in cell.Value. If the position is greater than 0, it means the text was found.
     • vbTextCompare makes the search case-insensitive, so it doesn’t matter whether searchText is in uppercase or lowercase.
   • If the searchText is found in the cell, the cell is highlighted by changing its interior color using cell.Interior.Color = highlightColor.
   • If the text is not found, the cell’s formatting is reset using cell.Interior.ColorIndex = xlNone.
4. Displaying a Message:
   • After the loop finishes, a message box pops up to let the user know that the highlighting process is complete. This is optional but can be helpful for user feedback.

How to Use:

1. Open Excel and press Alt + F11 to open the VBA editor.
2. In the VBA editor, go to Insert > Module to create a new module.
3. Copy and paste the above code into the new module.
4. Press F5 or go to Run > Run Sub/UserForm to execute the code.

Customizations:

• Range Adjustments: You can change Set rng = ws.Range("A1:D10") to any other range. For example:
  • Entire column: Set rng = ws.Columns("A")
  • Entire row: Set rng = ws.Rows("1")
  • Entire worksheet: Set rng = ws.UsedRange
• Text Matching: You can change searchText = "HighlightMe" to any text string you wish to highlight. If you want to search for multiple words, you could modify the code to handle that by using OR conditions.
• Highlight Color: Modify the highlightColor = RGB(255, 255, 0) line to change the color. You can use other RGB values like RGB(255, 0, 0) for red or RGB(0, 255, 0) for green.

Conclusion:

This macro allows you to quickly find and highlight cells containing a specific text within a defined range in an Excel worksheet. You can customize the range, search text, and highlight color as needed to suit your specific requirements.