如要对excel中A:B列的数据进行核对,A:B列数据可重复。要实现:
1)A:B列按升序排列
2)对每一行数据进行对比,一致时此行标绿色;a列值小于b列时,b列插入单元格,向下移动,此行标红色,;a列值大于b列时,a列插入单元格,向下移动,此行标红色。
Sub pptest()
Dim n As Integer, i As Integer
n = Application.CountA(Range("A:A"))
Worksheets("测试").Activate
Worksheets("测试").Range("A:A").Sort _
Key1:=Worksheets("测试").Cells(1, 1), order1:=xlAscending
Worksheets("测试").Range("B:B").Sort _
Key1:=Worksheets("测试").Cells(1, 2), order1:=xlAscending
For i = 1 To n
If Cells(i, 1) = Cells(i, 2) Then
Else
If Cells(i, 1).Value < Cells(i, 2).Value Then
Cells(i, 2).Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbov
n = n + 1
Else
Cells(i, 1).Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbov
n = n + 1
End If
End If
Next
End Sub
此部分循环有问题,进行到n最初值为止,不会随运转期间变化进行变动
Sub ppcolor()
Call pptest
Dim m As Integer, s As Integer
m = Application.Max(ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row, ActiveSheet.Range("B" & Rows.Count).End(xlUp).Row)
For s = 1 To m
If Cells(s, 1) = Cells(s, 2) Then
Range(Cells(s, 1), Cells(s, 2)).Interior.Color = RGB(0, 255, 0)
Else
Range(Cells(s, 1), Cells(s, 2)).Interior.Color = RGB(255, 0, 0)
End If
Next
Debug.Print n, m
End Sub
|