I have this Excel macro I created to highlight all instances of a number if at least one instance is already highlighted before running the macro.
Sub highlightXIDs()
Dim prods As Object: Set prods = CreateObject("Scripting.Dictionary")
Dim lastRow As Long: lastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
Dim tRange As Range
For Each tRange In ActiveSheet.Range("A2:A" & lastRow)
If tRange.Interior.ColorIndex <> xlNone Then prods.Add Key:=tRange.Value, Item:=tRange.Interior.Color
Next
Dim prod As Variant, xidMap As Object
Set xidMap = getXidMap(ActiveSheet.Range("A2:A" & lastRow))
For Each prod In prods.keys
xidMap(prod).EntireRow.Columns("A").Interior.Color = prods.Item(prod)
Next prod
End Sub
'get a "map" of each unique xid value to the rows containing it
Function getXidMap(rng As Range) As Object
Dim rv As Object, c As Range, currVal, cStart, i, tmp
Set rv = CreateObject("scripting.dictionary")
For Each c In rng.Cells
tmp = c.Value
If Len(tmp) > 0 Then
If rv.exists(tmp) Then
Set rv(tmp) = Application.Union(c, rv(tmp))
Else
rv.Add tmp, c
End If
End If
Next c
Set getXidMap = rv
End Function
Before:
After:
- Is this an efficient use of the included vba objects or should I change something?
For the line where I check the cell color, which would be more accurate/efficient in finding any cell that has fill color (excluding conditional formatting):
.Interior.ColorIndex <> xlNone
Or
.Interior.Color <> -4142
Or would these both work the same with the same amount of accuracy?