Public Sub Compare(fullname As String, sheet As String) Dim conn, sql, rows, i, cellContents ,rowIndex, colIndex rowIndex = 2 colIndex = getColumnIndex("Name")
'获取目标表数据
Set conn = CreateObject("adodb.connection")
conn.Open "provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='Excel 12.0';data source=" & fullname
sql = "select * from [" & sheet & "$]"
rows = conn.Execute(sql).getrows
conn.Close Do
'选择原表单元格
ActiveSheet.Cells(rowIndex, colIndex).Select
'去除填充色
With Selection.Interior
.Pattern = xlNone
.TintAndShade =
.PatternTintAndShade =
End With cellContents = ActiveCell.Value
If cellContents <> "" Then
For i = To UBound(rows, )
If cellContents = rows(, i) Then
If rows(, i) <> "apple" Then
'填充黄色
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color =
.TintAndShade =
.PatternTintAndShade =
End With End If
Exit For
End If
Next
End If rowIndex = rowIndex + Loop Until cellContents = ""End Sub