How to Highlight Cells in Excel?

Question

Suppose I have a cell value "Mobile achievement" so when I select this cell, all cells having both "mobile" & "achievement" values should get highlighted.

All these cells should get highlighted: "mobile achievement" or "achievement mobile" or "abc mobile achievement" or "mobile abc achievement" or "mobile abc xyz achievement" & so on.

My Question is: How to Highlight Cells in Excel directly with a function ?

Excel functions are not the sort of things which highlight cells. You can use conditional formatting rules which automatically highlight cells based on their contents and the formatting rules can be linked to a specific cell, but the cell used in the rules won't be changed merely by selecting a cell. There is a VBA solution, but it would be an event-handler (Worksheet_SelectionChange) rather than a function. — John Coleman

Karthick Gunasekaran Karthick Gunasekaran · Accepted Answer · 2016-04-06T12:56:13

you may use the below function to get your results. it will return the match count with the highlight.

Public Function highlightrange(texttocheck As String, r As Range)
    Dim totalmatchcount As Integer
    Dim matchcount As Integer
    r.Cells.Font.ColorIndex = 0
    matchcount = 0
    texttocheck = Replace(texttocheck, "  ", "")
    str1 = Split(texttocheck, " ")
    str2 = UBound(str1)
    For i = 1 To r.Cells.Count
        For j = 0 To str2
            If InStr(r.Cells(i), str1(j)) > 0 Then
                matchcount = matchcount + 1
            End If
        Next j
        If matchcount = str2 + 1 Then
            r.Cells(i).Font.ColorIndex = 8
            totalmatchcount = totalmatchcount + 1
        End If
        matchcount = 0
    Next i
    highlightrange = totalmatchcount
End Function

How to Highlight Cells in Excel?

1 Answers