| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Highlight duplicate cells based on a selected cell
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. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Highlight duplicate cells based on a selected cell
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. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Highlight duplicate cells based on a selected cell
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. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Highlight duplicates
This code is what I need but how would it be modified to highlight any dups. in Col A that also show up in Col Q too.
Mike wrote: slight changePrivate Sub Worksheet_SelectionChange(ByVal Target As 27-Mar-09 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: Previous Posts In This Thread: On Friday, March 27, 2009 3:55 PM igber wrote: Highlight duplicate cells based on a selected cell 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. On Friday, March 27, 2009 5:02 PM Mike wrote: Hi,Right click your sheet tab, view code and paste this in and try selecting 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: On Friday, March 27, 2009 5:05 PM Mike wrote: slight changePrivate Sub Worksheet_SelectionChange(ByVal Target As 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: Submitted via EggHeadCafe - Software Developer Portal of Choice BizTalk Repeating Structures Table Looping and Table Extract http://www.eggheadcafe.com/tutorials...g-structu.aspx |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Highlight duplicates
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 ElseIf Not IsError(Application.Match(Target.Value, Me.Columns("Q"), 0)) Then Columns(1).Interior.ColorIndex = xlNone Exit Sub End If End Sub -- HTH Bob <Ken Christman wrote in message ... This code is what I need but how would it be modified to highlight any dups. in Col A that also show up in Col Q too. Mike wrote: slight changePrivate Sub Worksheet_SelectionChange(ByVal Target As 27-Mar-09 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: Previous Posts In This Thread: On Friday, March 27, 2009 3:55 PM igber wrote: Highlight duplicate cells based on a selected cell 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. On Friday, March 27, 2009 5:02 PM Mike wrote: Hi,Right click your sheet tab, view code and paste this in and try selecting 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: On Friday, March 27, 2009 5:05 PM Mike wrote: slight changePrivate Sub Worksheet_SelectionChange(ByVal Target As 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: Submitted via EggHeadCafe - Software Developer Portal of Choice BizTalk Repeating Structures Table Looping and Table Extract http://www.eggheadcafe.com/tutorials...g-structu.aspx |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Highlight duplicate cells based on a selected cell | Excel Discussion (Misc queries) | |||
| Cells do not highlight when selected. | Excel Discussion (Misc queries) | |||
| Cells Won't Highlight when selected | Excel Discussion (Misc queries) | |||
| Autofill cell/sets of cells based upon value selected from a list. | Excel Discussion (Misc queries) | |||
| Cells no longer highlight when selected | Excel Discussion (Misc queries) | |||
Linear Mode
