|
- Sub test()
- Dim ws As Worksheet
- Dim r%, i%
- For Each ws In Worksheets
- With ws
- .Cells.Interior.ColorIndex = 0
- r = .Cells(.Rows.Count, 1).End(xlUp).Row
- arr = .Range("a1:l" & r)
- For i = 3 To UBound(arr)
- If Application.HLookup(arr(i, 3), [{"一级","二级","三级","四级","五级";12,10,9,7,5}], 2, False) <> arr(i, 4) Then
- .Cells(i, 4).Interior.ColorIndex = 3
- End If
- If arr(i, 7) <> arr(i, 4) + arr(i, 5) - arr(i, 6) Then
- .Cells(i, 7).Interior.ColorIndex = 4
- End If
- If i > 3 Then
- If arr(i, 8) <> arr(i - 1, 9) Then
- .Cells(i, 8).Interior.ColorIndex = 5
- End If
- End If
- If arr(i, 9) <> arr(i, 7) + arr(i, 8) Then
- .Cells(i, 9).Interior.ColorIndex = 6
- End If
- Select Case arr(i, 10)
- Case "一般奖励"
- If arr(i, 9) < 115 Then
- .Cells(i, 10).Interior.ColorIndex = 7
- End If
- Case "中级奖励"
- If arr(i, 9) < 130 Then
- .Cells(i, 10).Interior.ColorIndex = 7
- End If
- Case "高级奖励"
- If arr(i, 9) < 145 Then
- .Cells(i, 10).Interior.ColorIndex = 7
- End If
- End Select
- If i > 3 Then
- If DateDiff("m", arr(i - 1, 12), arr(i, 12)) <> 1 Then
- .Range("l3:l" & r).Interior.ColorIndex = 8
- .Cells(i, 12).Interior.ColorIndex = 9
- End If
- End If
- Next
- End With
- Next
- End Sub
复制代码 |
|