Highlight duplicate cells based on a selected cell
Hi Mike.
It works perfectly. Many thanks.
How does it work if If I also want to highlight another column in the same
workhsheet?
How does if work if I also want to highlight the range for the selected cell
A2? i.e. hightlight from Cell A2 to D2, A4 to D4, A5 to D5.
Column A B C D
1 aaa 224 471 773
2 bbb 555 632 444
3 8888 145 678 987
4 bbb 414 565 232
5 bbb 478 821 953
6 8888 231 599 636
7 8888 323 999 636
the highlight to apply to an
"Mike H" wrote:
slight change
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
If Target.Column < 1 Or Target.Row Lastrow Then
Columns(1).Interior.ColorIndex = xlNone
Exit Sub
End If
Set myrange = Range("A1:A" & Lastrow)
For Each c In myrange
If c.Value = Target.Value Then
c.Interior.ColorIndex = 7
Else
c.Interior.ColorIndex = xlNone
End If
Next
End Sub
Mike
"Mike H" wrote:
Hi,
Right click your sheet tab, view code and paste this in and try selecting
cells in Column A
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column < 1 Then
Columns(1).Interior.ColorIndex = xlNone
Exit Sub
End If
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Set myrange = Range("A1:A" & Lastrow)
For Each c In myrange
If c.Value = Target.Value Then
c.Interior.ColorIndex = 7
Else
c.Interior.ColorIndex = xlNone
End If
Next
End Sub
Mike
"igbert" wrote:
Is there a formula to highlight all duplicate cells from a selected cell?
Cell A1 aaa
Cell A2 bbb
Cell A3 8888
Cell A4 bbb
Cell A5 bbb
Cell A6 8888
Cell A7 8888
When I select bbb in Cell A2, I want the font in Cell A2, A4 and A5
highlighted. Likewise, if I select ccc in Cell A3, I want the font in Cell
A3, A6, A7 hightlighted.
|