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
- 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
.Interior.Color <> -4142
Or would these both work the same with the same amount of accuracy?