Can I add a dollar amount to duplicates to differentiate them?

Question

I have a huge sales data sheet from range D3 to BC1415 but here is my problem:

I have made several scorecards from this data sheet but duplicates are ruining everything. I have organised my scorecards in a way where Excel cannot see the difference between 15 apples that were sold during week A and 15 apples sold during week B(in my Top 15 week sales, the week A pops up two times and week B doesn't at all).

I'm halfway through the solution but I just don't have enough expertise in VBA yet to complete it:

I would like to add +0.001 to all duplicates except the first until there is no duplicate left in order to allow Excel to differentiate them. Would that be possible?

This doesn't seem to work but I believe it is the way to go:

• Conditional formatting=COUNTIF($A$1:A1,A1)>1 so only second and more duplicate occurrences get formatted.

• Then select all formatted cells with

---

Sub selectCFColours()
    Dim cell As Range
    Dim selRange As Range

    For Each cell In Range("A1:V1")
        If cell.DisplayFormat.Interior.Color <> cell.Interior.Color Then
            If selRange Is Nothing Then
                Set selRange = cell
            Else
                Set selRange = Union(selRange, cell)
            End If
        End If
    Next

    If Not selRange Is Nothing Then selRange.Select
End Sub

-Special pasting 0.001 with "Add" setting on all selected cells.

For some reasons this doesn't work.

Answer 1

I'm going to answer this question: "understand why my method doesn't work"

Your VBA is not working because it is selecting cells based on their interior.color value, but the colors set by conditional formatting don't populate the interior.color property.

That value is set to whatever the manual formatting is, and for your screen the conditional formatting is displayed on top.