subroutines not exectuing following external data refresh

[mcereno]

hi, i am a "true" novice here and looking for your advice. i have two subroutines that are supposed to A) compare two columns of data and (B) highlight data that is same. these are to run after an external web refresh occurs. at present, the subroutines are not working.

first, i want to pause the macro that contains A) and B) so the refresh can occur first before they run.

to pause the macro, i use:

Dim ws As Worksheet
Dim qt As QueryTable
For Each ws In ThisWorkbook.Worksheets
For Each qt In ws.QueryTables
qt.BackgroundQuery = False
qt.Refresh
Next qt
Next ws

then as per A) above, i use:

Private Sub CommandButton1_Click()
Dim CompareRange As Variant, To_Be_Compared As Variant, X As Variant, y As Variant

Range("A3").Select
Selection.End(xlDown).Select
Set To_Be_Compared = Range("A3:" & Selection.Address)
Range("F3").Select
Selection.End(xlDown).Select
Set CompareRange = Range("F3:" & Selection.Address)

iRow = 3

To_Be_Compared.Select

For Each X In Selection
For Each y In CompareRange
If X = y Then
Range("E" & iRow).Value = X
iRow = iRow + 1
End If
Next y
Next X

End Sub

then as per B) above, i use:

Private Sub test()
Dim rng As Range, c As Range, cfind As Range, rng1 As Range
Worksheets("ADExemptvsSCCM").Activate
Set rng = Range(Range("F3"), Range("F3").End(xlDown))
Set rng1 = Range(Range("E3"), Range("E3").End(xlDown))
For Each c In rng
Set cfind = rng1.Cells.Find(what:=c.Value, lookat:=xlWhole)
If Not cfind Is Nothing Then c.Interior.ColorIndex = 4
Next c
End Sub


i have placed the subroutines as follows:

With objExcel
Sheets("Sheet1").Select
Range("A3:A500").Clear

iRow = 3

DisplayMembers "LDAP://" & strGroupDN, strSpaces, dicSeenGroupMember

Columns(1).EntireColumn.AutoFit
Dim Replacement As String
Range("A:A").Select
Replacement = ActiveCell.Value
Range("A:A").Select
Selection.Replace what:="$", Replacement:="", lookat:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
RemoveDuplicates
CommandButton1_Click
test

End With

so ultmately, i want to compare the data in column F (external) to data in column A (macro that queries Active Directory) and place like data in column E (CommandButton1_Click
) then i want to highlight column F with like data from column E (test)

hopefully you can help me.

thank you!

[mcereno]

hi,

this problem has been resolved - the formatting of the external data was root cause and by using ltrim and rtrim, the macro ran as it should.

[mcereno]

hi,

this problem has been resolved - the formatting of the external data was root cause and by using ltrim and rtrim, the macro ran as it should.