VBA: Formatting the first duplicate of each set of duplicates in a column

[Shatnerfreude]

Hello,

This is my second posting for this problem... I apologize for "spamming".

I would please like a VB code for making the first duplicate black font in each group of duplicates in a column. I will set the rest of the column to white font as default. (See Example)
This is to avoid merging these duplicate cells, and could be very useful for others, as well. I currently have a code for automatically merging and top justifying sets of duplicates down the column (see below). Maybe it can be used as a template/starting place for What I need?

I have tried many combinations of conditional formatting, but nothing seems to work (although I have learned how to format the bottom duplicate).

Example:
Start with
a
a
a
a
b
b
b
c
c
c
c

Becomes
a
a
a
a
b
b
b
c
c
c
c

(Pretend the faded letters are invisible as white font)

Here is the code which automatically merges and top justifies groups of duplicates. I want to emulate the results of this without merging cells.

Sub MergeDupes()
Dim r As Range, c As Range
Dim i As Long, j As Long
Set r = Range("A1", Cells(Rows.Count, "A").End(xlUp))
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For i = 1 To r.Count
Set c = r(i)
j = 0
Do Until c <> c.Offset(rowoffset:=1)
Set c = c(2)
j = j + 1
Loop
With Range(r(i), c)
.Merge
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlTop
End With
i = i + j
Next i
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

THANK YOU!!!

 

[MickG]

If your data start "A2" then try:-

[COLOR="Navy"]Sub[/COLOR] MG08May50
[COLOR="Navy"]Dim[/COLOR] Lst [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
Lst = Range("A" & Rows.Count).End(xlUp).Row
[COLOR="Navy"]For[/COLOR] n = Lst To 2 [COLOR="Navy"]Step[/COLOR] -1
    [COLOR="Navy"]With[/COLOR] Range("A" & n)
        [COLOR="Navy"]If[/COLOR] Not .Offset(-1).Value = .Value [COLOR="Navy"]Then[/COLOR]
            .Font.Bold = True
        [COLOR="Navy"]Else[/COLOR]
            .Font.ColorIndex = 2
        [COLOR="Navy"]End[/COLOR] If
 [COLOR="Navy"]End[/COLOR] With
 [COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Shatnerfreude]

Hi Mick,
Thank you so much! It wasn't exactly what I needed, but I fixed it myself.
This made it do what I wanted:

Sub MG08May50()
Dim Lst As Long, n As Long
Lst = Range("A" & Rows.Count).End(xlUp).Row
For n = Lst To 2 Step -1
    With Range("A" & n)
        If Not .Offset(-1).Value = .Value Then
            .Font.Bold = False
        Else
            .Font.ColorIndex = 2
        End If
 End With
 Next n
End Sub

 

[MickG]

You're welcome

 

[Snakehips]

Just out of interest, this should do it with conditional formatting....

Assuming a header row
Rule 1 for format of bold
=COUNTIF($A$2:$A2,A2)=1

Rule 2 for format white font
=COUNTIF($A$2:$A2,A2)>1

 

[Shatnerfreude]

Thanks, Tony
I haven't tried it yet, but I appreciate the response!