DanteAmor was kind enough to provide the following macro to highlight duplicates and triplicates. It works on the Windows platform but not on MacOS.
On the MacOS it gets stuck on the line: Set dic1 = CreateObject(“Scripting.Dictionary”) and gives:
Run-time error ‘429’
ActiveX component cant’ create object
DanteAmore’s Macro is:
Sub Duplicates_3()
Dim c As Range, r As Range
Dim dic1 As Object, dic2 As Object
Dim j As Variant, ky As Variant
Application.ScreenUpdating = False
Set r = ActiveSheet.Range("C7:C12,J7:J12,Q7:Q12")
Set dic1 = CreateObject("Scripting.Dictionary")
Set dic2 = CreateObject("Scripting.Dictionary")
r.Interior.ColorIndex = 19
r.Font.Color = vbBlack
For Each c In r
dic2(c.Address) = c.Value
For j = c.Value - 4 To c.Value + 4
dic1(j) = dic1(j) + 1
Next
Next
'
For Each ky In dic2.keys
j = dic1(dic2(ky))
Select Case j
Case 2
Range(ky).Interior.ColorIndex = 3
Range(ky).Font.Color = vbWhite
Case Is >= 3
Range(ky).Interior.ColorIndex = 10
Range(ky).Font.Color = vbWhite
Case 4
'...
End Select
Next
End Sub
The Microsoft Support Website says the error message is because “the Automation object is unavailable to Visual Basic.”
Is there a way round this, or does it have to be rewritten for a Mac format?
I have tried various versions based on the above macro combined with my original macro that gives duplicates with a problem with triplicates without success.
I have Excel 16.37 on my Mac.
On the MacOS it gets stuck on the line: Set dic1 = CreateObject(“Scripting.Dictionary”) and gives:
Run-time error ‘429’
ActiveX component cant’ create object
DanteAmore’s Macro is:
Sub Duplicates_3()
Dim c As Range, r As Range
Dim dic1 As Object, dic2 As Object
Dim j As Variant, ky As Variant
Application.ScreenUpdating = False
Set r = ActiveSheet.Range("C7:C12,J7:J12,Q7:Q12")
Set dic1 = CreateObject("Scripting.Dictionary")
Set dic2 = CreateObject("Scripting.Dictionary")
r.Interior.ColorIndex = 19
r.Font.Color = vbBlack
For Each c In r
dic2(c.Address) = c.Value
For j = c.Value - 4 To c.Value + 4
dic1(j) = dic1(j) + 1
Next
Next
'
For Each ky In dic2.keys
j = dic1(dic2(ky))
Select Case j
Case 2
Range(ky).Interior.ColorIndex = 3
Range(ky).Font.Color = vbWhite
Case Is >= 3
Range(ky).Interior.ColorIndex = 10
Range(ky).Font.Color = vbWhite
Case 4
'...
End Select
Next
End Sub
The Microsoft Support Website says the error message is because “the Automation object is unavailable to Visual Basic.”
Is there a way round this, or does it have to be rewritten for a Mac format?
I have tried various versions based on the above macro combined with my original macro that gives duplicates with a problem with triplicates without success.
I have Excel 16.37 on my Mac.