Highlight duplicates
in [General]
|
From: Ken on 7 Apr 2010 10:15 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/aspnet/73bf7539-4c13-43a5-a580-a5704fe31a76/biztalk-repeating-structu.aspx
From: Bob Phillips on 7 Apr 2010 10:53 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 news:201047101538kchristman631(a)gmail.com... > 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/aspnet/73bf7539-4c13-43a5-a580-a5704fe31a76/biztalk-repeating-structu.aspx
|
Pages: 1 Prev: Multi-select from a dropdown list Next: Index/Match vs VLookup? |