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
Since you’re going to use a small bit of VBA anyway, I’d use the SelectionChange event to store the value of the active cell in another cell and use more basic conditional formatting to compare the stored value and the list values.
Also watch out for the CELL function – apparently it doesn’t refer to the host cell, but the most recently-changed cell.
http://www.dailydoseofexcel.com/archives/2011/08/09/cell-worksheet-function/
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}
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.
Matt