Highlight Cell Based on Number of Times its Value changes

[qadeerahmed]

I want to highlight the cell based on number of times its value changes
e.g.

IF the cell value changes 2nd time, it should highlight cell to "GREEN",
IF the cell value changes 3rd time, it should highlight cell to "BLUE", and so on...

Thanks for your precious time.

Ahmed.

[p45cal]

, and so on...
A bit short on detail; how does the sequence continue? (indigo then violet?!) Does it change on first time. Just a single cell in the whole sheet? Or a column, or a row? When does the colour get reset?

One way is to use conditional formatting in an area away from the normally-viewed part of the sheet.
In the attached I've got cells C2:C10 changing colour by copying the conditional format of cells 50 columns to the right. I've hidden lots of columns to bring them closer so you can see how it works. Normally you'd hide only column BA.

There are many other ways, which one's best? That depends on how you want the colouring to change. Many will involve some sort of macro running.

[qadeerahmed]

Thanks for your time. You are right that I was bit short on details, but this is what I was searching for.

I think, it would be more efficient to add condition to reset the colour of a particular cell after reaching the count to '10'. And clicking RESET button should reset the whole column, since this formatting is applied to a column not a single cell.

Regarding colouring , it would be much better to have a different prominent colour for each count.

Thanks again and appreciate for your precious time.

Ahmed.

[qadeerahmed]

Guru, any help?

[p45cal]

Oh, I thought it was solved ("but this is what I was searching for").
Remove conditional formatting from column BA
Replace the code with:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim RelevantCellsChanged As Range
Set RelevantCellsChanged = Intersect(Target, Range("C2:C13"))
If Not RelevantCellsChanged Is Nothing Then
For Each cll In RelevantCellsChanged
cll.Offset(, 50).Value = cll.Offset(, 50) + 1
If cll.Offset(, 50).Value = 11 Then cll.Offset(, 50).Value = 0
cll.Interior.ColorIndex = Choose(cll.Offset(, 50).Value + 1, xlNone, 3, 4, 5, 6, 7, 8, 15, 22, 23, 45)
Next cll
End If
End Sub

Sub ResetCount()
Set xx = Range("C2:C13")
xx.Offset(, 50).Value = 0
xx.Interior.ColorIndex = xlNone
End Sub
I've increased the range being monitored to row 13 to accommodate 10 different colours.

[qadeerahmed]

Awesome! Thanks a lot Guru.

I really appreciate for your precious time and helping me out. Stay Blessed.

Ahmed.