conditional Formatting based on value change

Houscoogs

New Member
Joined
Aug 19, 2005
Messages
15
I have a spreadsheet that has over 500 names in column A and I want the color to change once the names change.

Example:

Cell: A1 = John
A2 = John
A3 = Ben
A3 = Ben
A3 = Ben

I want the color of John to be the same and once the next cell down changed, I want it to color differently than the previous set.

Please help.
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
This highlights the list breaks:


Sub myfindGroups()
Dim myEnd As Boolean
Dim x&

Columns("A:A").Interior.ColorIndex = xlNone

x = 1

While Not myEnd
cRng = Cells(x, 1)
cRng2 = Cells(x + 1, 1)

If cRng2 = "" Then myEnd = True

If cRng = cRng2 And myFlag = False Then
n = Cells(x, 1).Row
myFlag = True
End If

If (((cRng <> cRng2) And n <> 1) Or (x = 1 And cRange <> cRng2)) Then

If x = 1 Then
If Cells(x, 1).Interior.ColorIndex <> 36 Then Cells(x, 1).Interior.ColorIndex = 36
n = 0
Else

If Cells(x, 1).Interior.ColorIndex <> 36 Then Cells(x, 1).Interior.ColorIndex = 36
n = n - 1
End If

MsgBox "Number of Records for: " & _
Cells(x, 1) & ", is " & Cells(x, 1).Row - n
myFlag = False
End If

x = x + 1
Wend
End Sub
 

Forum statistics

Threads
1,136,369
Messages
5,675,359
Members
419,565
Latest member
Phil57

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top