Mark duplicated cells by different (!) colors. How to?

[VitaliyATim]

I need mark (background color preferred) duplicated cells by different colors. I know how mark all mass of them by just one style - though autoformat, but I need different colors. Found few macros fo MS Excel for it, but no one of them compatible with LO Calc and no macros specially for LO Calc.

Normally, by my opinion each who try find duplicates want to know where they are and not just all mass of them. By my opinion it should be check box or by default autoformat mark them so. But it’s others.

[JohnSUN]

How many different colors can you use (distinguish)? If up to fifteen, then the solution already exists.

This was written five years ago (thanks @rami!)

[VitaliyATim]

It’s partly work. Only when cell is clear - no linked, no “=…”, no formula inside. If it so (most of part it is not clear) - macros crash.

Have you more universal script?

[sokol92]

From Russian forum:

Option Compatible 
Option Explicit 

' Formats duplicate cell values in different colors.
' - oDoc    document Calc. If missing then ThisComponent.
' - oRange  range(s) of cells to paint. All ranges must be on one sheet. If missing then CurrentSelection.
' - aColors array of colors.
'
' Empty cells are not processed.
' Idea: http://excelvba.ru/code/DuplicatesColors
Sub PaintDupl(Optional ByVal oDoc, Optional ByVal oRange, Optional ByVal aColors)
   Dim oCell, oCells, oMap, nCells As Long, nDupl As Long, ind as Long, s As String, i As Long
   If IsMissing(oDoc) Then oDoc=ThisComponent
   If IsMissing(oRange) Then oRange=oDoc.CurrentSelection
   If IsMissing(aColors) Then aColors=Array(12900829, 15849925, 14408946, 14610923, _
      15986394, 14281213, 14277081, 9944516, 14994616, 12040422, 12379352, 15921906, _
      14336204, 15261367, 14281213)
    
   If oRange.supportsService("com.sun.star.sheet.SheetCell") Then Exit Sub  ' одна ячейка
   oRange=oRange.queryContentCells(1+2+4+16)  ' number, date, string, formula
   oCells=oRange.Cells
   oMap=com.sun.star.container.EnumerableMap.create("string", "any")
   ind=-1
   
   For Each oCell In oCells
     nCells=nCells+1
     s=oCell.String
     If s<>"" Then
       If oMap.containsKey(s) Then
         i=oMap.get(s)
         If i=-1 Then
           ind=ind+1
           If ind>Ubound(aColors) Then ind=0
           oMap.put s, aColors(ind)
           nDupl=nDupl+2
         Else
           nDupl=nDupl+1  
         End If
       Else
         oMap.put s, -1
       End If  
     End If       
   Next oCell 

   For Each oCell In oCells
     s=oCell.String
     If s<>"" Then 
       i=oMap.get(s)
       If oCell.CellBackColor<>i Then oCell.CellBackColor=i
     End If
   Next oCell    
       
   Msgbox "Count of Cells: " & nCells & " duplicates: " & nDupl
End Sub

' --------------------------------------------------------------------------------------------------------------------
' Paint Selection
Sub PaintDuplSelection
  PaintDupl
End Sub