Re: help to use wild card to compare two columns on two sheets and highlight cells
kbotta,
Detach/open Compare_Combined_colB_Sheet2_colA rngobjects - kbotta - OZ188799 - SDG15.xlsm and run the Compare_Combined_colB_Sheet2_colA macro.
I am not able to add/upload a new file.
Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).
1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.
Sub Compare_Combined_colB_Sheet2_colA()
' Stanley D. Grom, 06/05/2014, OZ188799
Dim wc As Worksheet, w2 As Worksheet
Dim c As Range, arng As Range
Application.ScreenUpdating = False
Set wc = Sheets("Combined")
Set w2 = Sheets("Sheet2")
With wc
For Each c In .Range("B2", .Range("B" & Rows.Count).End(xlUp))
Set arng = w2.Columns(1).Find(c & "*", LookAt:=xlWhole)
If arng Is Nothing Then
c.Interior.Color = vbRed
End If
Set arng = Nothing
Next c
End With
Application.ScreenUpdating = True
End Sub
Display More
Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm
Then run the Compare_Combined_colB_Sheet2_colA macro.