- Sub lqt()
- tms = Timer
- r = Cells(Rows.Count, 32).End(3).Row
- Range("w1:ac" & r).Clear
- arr = Range("af1:ah" & r)
- brr = Range("ak1:am" & r)
- crr = Range("w1:y" & r)
- drr = Range("aa1:ac" & r)
- Application.ScreenUpdating = False
- For i = 3 To r Step 4
- For j = 1 To 3
- If arr(i, j) <> "" And brr(i, j) <> "" Then
- crr(i - 1, j) = arr(i - 1, j): drr(i - 1, j) = brr(i - 1, j)
- If arr(i, 1) = brr(i, 1) Then
- crr(i, 1) = "参照"
- If arr(i, 2) > arr(i, 1) Then
- crr(i, 2) = "大于参照"
- ElseIf arr(i, 2) < arr(i, 1) Then
- crr(i, 2) = "小于参照"
- End If
- If arr(i, 3) > arr(i, 1) Then
- crr(i, 3) = "大于参照"
- ElseIf arr(i, 3) < arr(i, 1) Then
- crr(i, 3) = "小于参照"
- End If
- End If
- If arr(i, 2) = brr(i, 2) Then
- crr(i, 2) = "参照"
- If arr(i, 2) < arr(i, 1) Then
- crr(i, 1) = "大于参照"
- ElseIf arr(i, 2) > arr(i, 1) Then
- crr(i, 1) = "小于参照"
- End If
- If arr(i, 3) > arr(i, 2) Then
- crr(i, 3) = "大于参照"
- ElseIf arr(i, 3) < arr(i, 2) Then
- crr(i, 3) = "小于参照"
- End If
- End If
- If arr(i, 3) = brr(i, 3) Then
- crr(i, 3) = "参照"
- If arr(i, 1) < arr(i, 3) Then
- crr(i, 1) = "小于参照"
- ElseIf arr(i, 1) > arr(i, 3) Then
- crr(i, 1) = "大于参照"
- End If
- If arr(i, 3) < arr(i, 2) Then
- crr(i, 2) = "大于参照"
- ElseIf arr(i, 3) > arr(i, 2) Then
- crr(i, 2) = "小于参照"
- End If
- End If
- If arr(i + 1, 1) = brr(i + 1, 1) Then
- crr(i + 1, 1) = "参照"
- If arr(i + 1, 2) > arr(i + 1, 1) Then
- crr(i + 1, 2) = "大于参照"
- ElseIf arr(i + 1, 2) < arr(i + 1, 1) Then
- crr(i + 1, 2) = "小于参照"
- End If
- If arr(i + 1, 3) > arr(i + 1, 1) Then
- crr(i + 1, 3) = "大于参照"
- ElseIf arr(i + 1, 3) < arr(i + 1, 1) Then
- crr(i + 1, 3) = "小于参照"
- End If
- End If
- If arr(i + 1, 2) = brr(i + 1, 2) Then
- crr(i + 1, 2) = "参照"
- If arr(i + 1, 2) < arr(i + 1, 1) Then
- crr(i + 1, 1) = "大于参照"
- ElseIf arr(i + 1, 2) > arr(i + 1, 1) Then
- crr(i + 1, 1) = "小于参照"
- End If
- If arr(i + 1, 3) > arr(i + 1, 2) Then
- crr(i + 1, 3) = "大于参照"
- ElseIf arr(i + 1, 3) < arr(i + 1, 2) Then
- crr(i + 1, 3) = "小于参照"
- End If
- End If
- If arr(i + 1, 3) = brr(i + 1, 3) Then
- crr(i + 1, 3) = "参照"
- If arr(i + 1, 1) < arr(i + 1, 3) Then
- crr(i + 1, 1) = "小于参照":
- ElseIf arr(i + 1, 1) > arr(i + 1, 3) Then
- crr(i + 1, 1) = "大于参照"
- End If
- If arr(i + 1, 3) < arr(i + 1, 2) Then
- crr(i + 1, 2) = "大于参照"
- ElseIf arr(i + 1, 3) > arr(i + 1, 2) Then
- crr(i + 1, 2) = "小于参照"
- End If
- End If
- If arr(i, 1) = brr(i, 1) Then
- drr(i, 1) = "参照"
- If brr(i, 2) > brr(i, 1) Then
- drr(i, 2) = "大于参照"
- ElseIf brr(i, 2) < brr(i, 1) Then
- drr(i, 2) = "小于参照"
- End If
- If brr(i, 3) > brr(i, 1) Then
- drr(i, 3) = "大于参照"
- ElseIf brr(i, 3) < brr(i, 1) Then
- drr(i, 3) = "小于参照"
- End If
- End If
- If arr(i, 2) = brr(i, 2) Then
- drr(i, 2) = "参照"
- If brr(i, 2) < brr(i, 1) Then
- drr(i, 1) = "大于参照"
- ElseIf brr(i, 2) > brr(i, 1) Then
- drr(i, 1) = "小于参照"
- End If
- If brr(i, 3) > brr(i, 2) Then
- drr(i, 3) = "大于参照"
- ElseIf brr(i, 3) < brr(i, 2) Then
- drr(i, 3) = "小于参照"
- End If
- End If
- If arr(i, 3) = brr(i, 3) Then
- drr(i, 3) = "参照"
- If brr(i, 1) < brr(i, 3) Then
- drr(i, 1) = "小于参照"
- ElseIf brr(i, 1) > brr(i, 3) Then
- drr(i, 1) = "大于参照"
- End If
- If brr(i, 3) < brr(i, 2) Then
- drr(i, 2) = "大于参照"
- ElseIf brr(i, 3) > brr(i, 2) Then
- drr(i, 2) = "小于参照"
- End If
- End If
- If arr(i + 1, 1) = brr(i + 1, 1) Then
- drr(i + 1, 1) = "参照"
- If brr(i + 1, 2) > brr(i + 1, 1) Then
- drr(i + 1, 2) = "大于参照"
- ElseIf brr(i + 1, 2) < brr(i + 1, 1) Then
- drr(i + 1, 2) = "小于参照"
- End If
- If brr(i + 1, 3) > brr(i + 1, 1) Then
- drr(i + 1, 3) = "大于参照"
- ElseIf brr(i + 1, 3) < brr(i + 1, 1) Then
- drr(i + 1, 3) = "小于参照"
- End If
- End If
- If arr(i + 1, 2) = brr(i + 1, 2) Then
- drr(i + 1, 2) = "参照"
- If brr(i + 1, 2) < brr(i + 1, 1) Then
- drr(i + 1, 1) = "大于参照"
- ElseIf brr(i + 1, 2) > brr(i + 1, 1) Then
- drr(i + 1, 1) = "小于参照"
- End If
- If brr(i + 1, 3) > brr(i + 1, 2) Then
- drr(i + 1, 3) = "大于参照"
- ElseIf brr(i + 1, 3) < brr(i + 1, 2) Then
- drr(i + 1, 3) = "小于参照"
- End If
- End If
- If arr(i + 1, 3) = brr(i + 1, 3) Then
- drr(i + 1, 3) = "参照"
- If brr(i + 1, 1) < brr(i + 1, 3) Then
- drr(i + 1, 1) = "小于参照"
- ElseIf brr(i + 1, 1) > brr(i + 1, 3) Then
- drr(i + 1, 1) = "大于参照"
- End If
- If brr(i + 1, 3) < brr(i + 1, 2) Then
- drr(i + 1, 2) = "大于参照"
- ElseIf brr(i + 1, 3) > brr(i + 1, 2) Then
- drr(i + 1, 2) = "小于参照"
- End If
- End If
- End If
- Next
- Next
- Range("w1:y" & r) = crr
- Range("aa1:ac" & r) = drr
- With [w1].Resize(r, 3)
- For i = 3 To r Step 4
- .Cells(i, 1).Resize(2, 3).Font.Bold = True
- .Cells(i, 1).Resize(2, 3).HorizontalAlignment = xlCenter
- Next
- For i = 3 To r
- For j = 1 To 3
- With .Cells(i, j)
- If crr(i, j) = "大于参照" Then
- .Font.ColorIndex = 6
- .Interior.ColorIndex = 3
- ElseIf crr(i, j) = "小于参照" Then
- .Font.ColorIndex = 6
- .Interior.ColorIndex = 7
- ElseIf crr(i, j) = "参照" Or crr(i, j) = "等于参照" Then
- .Font.ColorIndex = 1
- .Interior.ColorIndex = 40
- End If
- End With
- Next
- Next
- End With
- With [aa1].Resize(r, 3)
- For i = 3 To r Step 4
- .Cells(i, 1).Resize(2, 3).Font.Bold = True
- .Cells(i, 1).Resize(2, 3).HorizontalAlignment = xlCenter
- Next
- For i = 3 To r
- For j = 1 To 3
- With .Cells(i, j)
- If drr(i, j) = "大于参照" Then
- .Font.ColorIndex = 6
- .Interior.ColorIndex = 3
- ElseIf drr(i, j) = "小于参照" Then
- .Font.ColorIndex = 6
- .Interior.ColorIndex = 7
- ElseIf drr(i, j) = "参照" Or crr(i, j) = "等于参照" Then
- .Font.ColorIndex = 1
- .Interior.ColorIndex = 40
- End If
- End With
- Next
- Next
- End With
- Application.ScreenUpdating = True
- MsgBox "比较完成!耗时:" & Timer - tms & "秒"
- End Sub
复制代码
用个笨办法,终于勉强完成了: |