How to Highlight Cells Containing Current Cell's Text

Question

I have looked at conditional formatting and from what I can see it must be open in excel when highlighting specific cells matching a rule across a range.

Is there a way for an excel spreadsheet to have some form of rule that will check the current cell's text value against all other cells within a specific range and highlight all cell's matching the current cell's text value?

I am creating a giant appointment schedule for an event and I want people to be able to click on one of their appointments and the rest of their appointments will be highlighted throughout the schedule. As soon as you click on a different cell holding a different company name all cells with the text value of that company name will be highlighted instead.

Each row represents a company (around 50) and each column will represent an appointment slot (i.e. 9:15-9:30). Each cell will hold the name/code of a buyer who will be meeting the corresponding company at the matching appointment time. As you can imagine this can get very messy and I believe the ability to highlight all appointments for each buyer on an ongoing basis would be useful.

How do I put this together and is it even possible?

Yes it's possible. You would have to write VBA code that read the value of the clicked cell (Use the Worksheet's SelectionChange event for that), then performed a Range.Find using that value across the whole range of cells containing the appointments) and for each cell found, set the cell's .interior.backgroundcolor property to suit your requirements. You'd also want to set all cell backgrounds to white before you did the find or you'll leave all clicked cells coloured as you go through each one. — Dave
That should be enough to get you started - normally this would have been a "show us what you have tried" type comment only, but it IS Xmas (nearly), so go forth and investigate the above. And if you are having trouble still, come back, update with your code and we'll see if we can't get you squared away. — Dave
@Dave Nice comment! Just to mention, it would be faster if gather all Find-ranges to one with Union first, and then do what you described . — CommonSense
@CommonSense I'd just set the Range to Find to cover the whole appointments cell area, then loop through each match with .FindNext to act on them all. — Dave
@Dave, yes, I seen this, but paint one mega range Unioned from all .Find and .FindNext ranges is faster than "act on them all" one by one. Am i wrong? — CommonSense

Darren Bartrup-Cook Darren Bartrup-Cook · Accepted Answer · 2016-12-22T14:25:51

As it is Christmas... update your Conditional Formatting using the Worksheet_SelectionChange event.

You may want to add a check for blank cells - it will highlight all blank cells in the range when selected (yellow background, red text).

Something like:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Dim rngAppointments As Range

    If Target.Cells.Count = 1 Then
        Set rngAppointments = Target.Parent.Range("A1:D50")
        If Not Intersect(Target, rngAppointments) Is Nothing Then
            With rngAppointments
                With .FormatConditions
                    .Delete
                    .Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=""" & Target.Value & """"
                End With
                .FormatConditions(.FormatConditions.Count).SetFirstPriority
                With .FormatConditions(1)
                    With .Font
                        .Bold = True
                        .Color = -16776961
                    End With
                    With .Interior
                        .Color = 65535
                    End With
                End With
            End With
        End If
    End If
End Sub

How to Highlight Cells Containing Current Cell's Text

1 Answers