Highlight matches to the current cell

I came across a very reasonable request from someone who wanted to see which entries in a list matched those in the current cell (http://answers.microsoft.com/en-us/office/forum/office_2010-excel/event/49aa9987-3cf5-4007-9f08-df076ff0beba). While the original request dealt with names, I abstracted the problem into a set of numbers. Column A in Figure 1 is one list of numbers. Column C represents a list that we want to check against column A. Selecting a cell in column C should highlight all the matches in column A.

Figure 1

For a version in a page by itself (i.e., not in a scrollable iframe as below) visit http://www.tushar-mehta.com/publish_train/xl_vba_cases/0702 Highlight matches to current cell.shtml

Tushar Mehta


    • Thank you for your comments, Yard.

      As I noted in the original post, doing anything more in VBA ties format/layout changes in the worksheet to corresponding changes to the code. It reduces consumer flexibility.

      As far as the 2nd comment goes, as I noted in my comment to Dick Kusleika’s post, CELL without the 2nd argument works with the *active* cell at the time of recalculation, not the last changed cell.

      And, the solution in this post relies on Excel using the active cell in its calculation! So, in the context of this post, there’s nothing to watch out for. {grin}

  1. I used a similar method to Yard for this, except that rather than storing a value in the worksheet I stored it in a worksheet name:-

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Target, Range(“C1:C10″)) Is Nothing Then
    ActiveWorkbook.Names.Add “MyName”, Target
    End If
    End Sub

    That way it was fairly straighforward to use conditional formatting to check the value of ‘MyName’ and change the colours. I like your method better though – clever stuff.


Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>