EXCEL实用VBA宏代码.doc

1 删除内容重复的行 Sub DeleteRow() 删除内容重复的行 Dim R As Integer Dim i As Integer With Sheet1 R = .[a65536].End(xlUp).Row For i = R To 1 Step -1 If WorksheetFunction.CountIf(.Columns(1), .Cells(i, 1)) 1 Then .Rows(i).Delete End If Next End With End Sub 2 自动监视单元格 Private Sub Worksheet_Change(ByVal Target As Range) Dim j, k, col As Integer j = Range(QTYS).Column Range(iCol).Column k = Target.Row end_col = Range(end_cols).Column 13 在这里调整颜色终止的最后一列 col = Range(sum).Column Stop qty = Target.Value With Target If .Count = 1 And .Row 13 And .Column col Then If Cells(k, col).Value = Cells(k, j).Value Then IsNumeric(Cells(k, col)) And Application.EnableEvents = False With Range(Cells(k, 2), Cells(k, end_col)).Interior .ColorIndex = 4 green .Pattern = xlSolid End With Application.EnableEvents = True ElseIf Cells(k, col).Value 0 And Cells(k, col).Value Cells(k, j) Then Application.EnableEvents = False With Range(Cells(k, 2), Cells(k, end_col)).Interior .ColorIndex = 6 yellow .Pattern = xlSolid xlNone End With Application.EnableEvents = True ElseIf Cells(k, col).Value Cells(k, j).Value Then Application.EnableEvents = False With Range(Cells(k, 2), Cells(k, end_col)).Interior .ColorIndex = 15 .Pattern = xlSolid xlNone End With Application.EnableEvents = True MsgBox Actual Qty: qty Order: Cells(k, j).Value or Wrong ? ElseIf Cells(k, col).Value = 0 Or Cells(k, col).Value = Then Application.EnableEvents = False With Range(Cells(k, 2), Cells(k, end_col)).Interior .ColorIndex = 15 .Patt