7 thoughts on “Highlight matches to the current cell”

1. Being lazy here, but how might one modify the code to only recalculate on selection change for a specific range? For example, in your sample worksheet, limit the “monitored” cells to the range A1:A20. That way, unnecessary recalculations aren’t being initiated…

2. Not too complicated I suppose:

   Private Sub Worksheet_SelectionChange(ByVal Target As Range)
     Target.Name = “tst”
     If Target.Column = 3 And Target <> “” Then Range(Join(Filter([transpose(if(A1:A100=tst,address(row(A1:A100),1),“”))], “$”), “,”)).Select
   End Sub

3. That is actually very cool and can be a great tool in meetings that involve Excel. Gret for presentations, but also great for analysis. It really saves the brain and the eyes.

   Christopher

4. I like the conditional formatting formula. I modified it to highlight a specific cell in the same row. However it did not automatically highlight the cell because my calculation is set to manual. I had to add the “Calculate” command right after the “Target.Calculate” in the code module. Then it worked for me.

5. Hi Tushar

   That’s a cool trick.

   Just nit-picking but I just use the TRUE condition for Conditional Formatting.

   So

   =IF(CELL(“col”)=COLUMN(C:C),A1=CELL(“contents”))

   should work.

   To my eyes, I’d like to see cells in Column A with matching values highlighted only when cells in Column C are selected, otherwise show without formatting.

   So I would use 2 conditonal formats.

   The “shady” one would be

   =AND(CELL(“col”)=3,CELL(“row”)<21,A1=CELL(“contents”))

   And the “clear” one with no formatting would be

   =OR(CELL(“col”)<>3,CELL(“row”)>20)

   .

   The Worksheet_SelectionChange code would calculate the entire sheet but to make it range specific, I could use something like

   Private Sub Worksheet_SelectionChange(ByVal Target As Range)
       If Not Intersect(Target, Range(“A1:F20”)) Is Nothing Then Range(“A1:F20”).Calculate
   End Sub

   Hope I got the vb tags right :-)

6. Hi Tushar,

   we can add *Now() to calculate instade of Worksheet_SelectionChange
   =IF(CELL(“col”)=COLUMN(C:C),A1=CELL(“contents”),FALSE)*NOW()

7. Tushar
   Please ignore my previous comment.its my mistake.

   Sudhir.

Posting code? Use <pre> tags for VBA and <code> tags for inline.