I’m sharing some code today for a project where we built a custom add-in that could handle logic for advanced matching criteria.  Usually, the requirement is to match on the values of a certain field, ie a vlookup.  Sometimes, it gets more complex, such as matching on multiple fields, or matching on an extracted portion of a cell.  In this case, the logic was to match on a category value, then to match on a color if there were multiple matches.  We delivered an add-in for this

blog2

Some of the code:

For lngR = LBound (VarRow) To UBound (VarRow)

If VarRow (lngR, lngCol) <> “” Then

ObjDic.Item (VarRow (lngR, lngCol)) = ObjDic.Item (VarRow (lngR, lngCol)) & “,” & lngR

End If

Next lngR

VarRow = ObjDic.items

 

For Each varEle In VarRow

VarTemp = Split (Right (varEle, Len (varEle) – 1), “,”)

If UBound (varTemp) > 0 Then

For Each k in varTemp

.Range (“A1”).CurrentRegion.Rows (Int (k)).Interior.Color = lngColor

.Cells (Int (k), .Cells (1, Columns.Count).End (xlToLeft).Offset (, 1).Column).Value = bytSortNumber

Next k

End If

Next varEle

View Our Impressive List of Clients
Back to Home Page
Contact Us for a Free Quote