Highlight all the rows containing same name

Closed
Janie - Apr 18, 2011 at 01:24 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Apr 18, 2011 at 09:10 AM
Hello,

I have an excel in which one of the column(D) has duplicate entries for name.If clicked
on a particular name, I want to highlight all the rows containing same name.

Please help me writing the macro for this as i am completely new to it.


Thanks in advance
Janie

2 responses

eskagsms Posts 24 Registration date Wednesday February 9, 2011 Status Member Last seen May 30, 2012
Apr 18, 2011 at 02:19 AM
Hi, you could use conditional formatting option from the menu. Hope this helps.
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Apr 18, 2011 at 09:10 AM
Hi Janie,

Macro's can NOT be undone by hitting the blue arrow. So SAVE your file before running or in this case implementing codes, so you are able to restore your file.

Try this code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Intersect(Target, Range(Range("D1"), Range("D" & Rows.Count).End(xlUp))) Is Nothing Then Exit Sub
Cells.Interior.ColorIndex = xlNone
Set MR = Range(Range("D1"), Range("D" & Rows.Count).End(xlUp))
For Each cell In MR
If cell.Value = ActiveCell.Value Then
cell.EntireRow.Interior.ColorIndex = 50
    End If
    Next
End Sub

To implement the code right-click on the sheets tab and select view code.
A new window (Microsoft Visual Basic) will open, now paste the code in the big white field.

The code will run automatically when you double-click a cell in the range of D1 to the last cell used in column D.

You didn't specify a color, so I chose green.
To pick your own color, record a macro, color a cell, stop recording and find the ColorIndex value in VB.

PLEASE NOTE that all colors will be removed from the sheet when the code is activated!

Hope you enjoy the result.

Best regards,
Trowa
0