VBA: automatically give color to next cell in a list/column if different from above/previous (distinct)

[ooptennoort]

To enhance visibility when looking through a list/column of slightly changing codes I want to make sure my eyes see when a code has changed, by coloring the distincts.

I want to: run a macro that gives my selection of cells a (unique?) (random?) color for each distinct cell value.

Anyone have a macro for this? Is it possible at all?

 

[Fluff]

Hi & welcome to MrExcel.
Rather than separate colours how about alternating colours

Sub ooptennoort()
   Dim Cl As Range
   
   With CreateObject("scripting.dictionary")
      For Each Cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
         If Not .Exists(Cl.Value) Then .Add Cl.Value, (.Count Mod 2) + 36
         Cl.Interior.ColorIndex = .Item(Cl.Value)
      Next Cl
   End With
End Sub

 

[ooptennoort]

Hi & welcome to MrExcel.
Rather than separate colours how about alternating colours

Sub ooptennoort()
   Dim Cl As Range
  
   With CreateObject("scripting.dictionary")
      For Each Cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
         If Not .Exists(Cl.Value) Then .Add Cl.Value, (.Count Mod 2) + 36
         Cl.Interior.ColorIndex = .Item(Cl.Value)
      Next Cl
   End With
End Sub

Mmmh, and then it dawned on me why I could not find anything reg what I wanted Thank you! You're right of course: alternating works just as well!!! Stupid of me
Now your vba is hard coded for A2, but how would I write this to be applied to any cells (even if not bordering (i.e. adjacent)) that I select (selection)?
Thx again!

 

[Fluff]

How about

Sub ooptennoort()
   Dim Cl As Range
   
   With CreateObject("scripting.dictionary")
      For Each Cl In Selection
         If Not .Exists(Cl.Value) Then .Add Cl.Value, (.Count Mod 2) + 36
         Cl.Interior.ColorIndex = .Item(Cl.Value)
      Next Cl
   End With
End Sub

 

[ooptennoort]

How about

Sub ooptennoort()
   Dim Cl As Range
  
   With CreateObject("scripting.dictionary")
      For Each Cl In Selection
         If Not .Exists(Cl.Value) Then .Add Cl.Value, (.Count Mod 2) + 36
         Cl.Interior.ColorIndex = .Item(Cl.Value)
      Next Cl
   End With
End Sub

So simple yet sooo convenient! Thank you so much!

 

[ooptennoort]

So simple yet sooo convenient! Thank you so much!

(Like your choice of colors!)

 

[Fluff]

You're welcome & thanks for the feedback.
It's possible to do different colours, as you wanted, the problem is some of them may be quite dark & so you would need to change the font colour as well.

 

[ooptennoort]

You're welcome & thanks for the feedback.
It's possible to do different colours, as you wanted, the problem is some of them may be quite dark & so you would need to change the font colour as well.

From a layman's perspective, writing vba to change the font too (nested IF in vba?) is most likely 'stacked complexity'. If that is true it might seem 'easier' maybe to 'just' not use dark colours? If possible, this would, however, limit the number of usable colours considerably & thereby perhaps limit the number of colourable (changed) cells? Unless the colouring would restart when that limit is reached, reusing colours? (Thinking out loud.) But this too is over my head and your solution is already great!