Conditional formatting

[puiuluipui]

Hi, i need a way to use conditional formatting or another way to highlight
cells based on duplicate names group.
Ex:

1 john
2 john
3 john
4 mary
5 mary
6 jim
7 cris
8 cris
9 cris

I need all cells with john to be highlighted with one colour, the next group
(mary) with another color, the next group (jim) with another color....and so
on.
All names will be ascending.
Can this be done?
Thanks!

 

[Jacob Skaria]

Try the below macro with data in ColA

Sub Macro1()
Dim lngRow As Long, blnChange As Boolean
For lngRow = 2 To Cells(Rows.Count, "A").End(xlUp).Row
If Range("A" & lngRow) <> Range("A" & lngRow - 1) Then blnChange = Not
blnChange
Range("A" & lngRow).Interior.ColorIndex = IIf(blnChange = False, xlNone, 15)
Next
End Sub

 

[Jacob Skaria]

If the previous one gives you syntax error try the below version

Sub Macro1()
Dim lngRow As Long, blnChange As Boolean
For lngRow = 2 To Cells(Rows.Count, "A").End(xlUp).Row
If Range("A" & lngRow) <> Range("A" & lngRow - 1) Then
blnChange = Not blnChange
End If
Range("A" & lngRow).Interior.ColorIndex = IIf(blnChange = False, xlNone, 15)
Next
End Sub

 

[puiuluipui]

It's working great!. I have one more question. I need to see how many times
john appear (3), mary (2).....an so on. I need to count the duplicates.
How can i do this?
Thanks!

"Jacob Skaria" a scris:

 

[Jacob Skaria]

Try the below version which will put in the count in ColB for every last
unique entry

Sub Macro1()
Dim lngRow As Long, blnChange As Boolean, intCounter As Integer
For lngRow = 2 To Cells(Rows.Count, "A").End(xlUp).Row
intCounter = intCounter + 1
If Range("A" & lngRow) <> Range("A" & lngRow - 1) Then
blnChange = Not blnChange
Range("B" & lngRow - 1) = intCounter: intCounter = 0
End If
Range("A" & lngRow).Interior.ColorIndex = IIf(blnChange = False, xlNone, 15)
Next
Range("B" & lngRow - 1) = intCounter + 1
End Sub

 

[puiuluipui]

It's working, but i need the macro to put the count in ColB for every first
unique entry, because i need to run advance filter and to remain with unique
entries. The advance filter keeps only the first record, so i need this macro
to put the count in ColB for every first unique entry, and then to run
advance filter.
Can this be done?
Thanks!


"Jacob Skaria" a scris:

 

[Jacob Skaria]

Try

Sub Macro1()
Dim lngRow As Long, blnChange As Boolean, intCount As Integer, lngFRow As Long
lngFRow = 1
For lngRow = 2 To Cells(Rows.Count, "A").End(xlUp).Row
intCounter = intCounter + 1
If Range("A" & lngRow) <> Range("A" & lngRow - 1) Then
blnChange = Not blnChange
Range("B" & lngFRow) = intCount: intCount = 0
lngFRow = lngRow
End If
Range("A" & lngRow).Interior.ColorIndex = IIf(blnChange = False, xlNone, 15)
Next
Range("B" & lngFRow) = intCount + 1
End Sub

 

[puiuluipui]

Hi, the code put the result in the first cell, but the result is 0.
Thanks!

"Jacob Skaria" a scris: