excel两个表比较的vba代码



Sub Macro1()

‘ Macro1 Macro

‘ 宏由 网络蛀虫制作,时间: 2011-4-25

‘sheet1表包含sheet2表的内容

根据sheet1表a列的数值对比查找sheet2表里数值相同的sheet1表sheet2表俊设置单元格的颜色为红色,

sheet1表中

A列值不同的设置单元格颜色为黄色

 

Dim i, j As Integer ‘定义文件名称长度临时变量i,j

Dim text1 As String

i = 1 ‘设置sheet1中A列要比较的起始行变量

j = 20 ‘

设置sheet1中A列要比较的

结束行变量

 

For i = 1 To j

 

Sheets(“Sheet1”).Select ‘激活表sheet1

Range(“A” & i).Select

text1 = Range(“A” & i).Value ‘取得选中单元格的数值

Debug.Print text1 ‘立即窗口输出值

Sheets(“Sheet2”).Select ‘激活表sheet2

Set rngFind = Cells.Find(What:=text1, After:=ActiveCell, LookIn:= _

xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext _

, MatchCase:=False, MatchByte:=False, SearchFormat:=False) ‘在激活表中设置查找语句

If rngFind Is Nothing Then ‘对查找结果进行If判断

‘没有找到数据的处理

Sheets(“Sheet1”).Select ‘激活表sheet1

With Selection.Interior

.ColorIndex = 6

.Pattern = xlSolid

End With

Debug.Print “没有找到” & text1

Else

Debug.Print “找到” & text1

rngFind.Activate ‘把查找到的数据设置为选中状态

With Selection.Interior

.ColorIndex = 3

.Pattern = xlSolid

End With

Sheets(“Sheet1”).Select ‘激活“Sheet2”工作表

With Selection.Interior

.ColorIndex = 3

.Pattern = xlSolid

End With

End If ‘查找结果If判断结束

Next

End Sub

 » 本站地址:http://www.gomoth.com

标签: