如何使 Excel 宏更短？

Question

这是我的代码的作用：它在列中查找重复的条目A。当发现重复项时，它会将颜色应用于两个单元格。

我想要实现的目标：

• 减少代码量
• 简短而亲切

---

Sub COLOUR_DOUBLE_ENTRY()

Application.Workbooks(file_name).Worksheets("ms").Activate

last_row = Application.Workbooks(file_name).Worksheets("ms").Range("a65536").End(xlUp).Row

   Application.Workbooks(file_name).Worksheets("ms").Range("A:E").Interior.Pattern = xlNone



For a = 2 To last_row
For b = 1 To last_row

'NAME
 first_item = Application.Workbooks(file_name).Worksheets("ms").Range("b" & a).Value
 secound_item = Application.Workbooks(file_name).Worksheets("ms").Range("b" & b + a).Value

'VALUE
first_item_value = Application.Workbooks(file_name).Worksheets("ms").Range("C" & a).Value
secound_item_value = Application.Workbooks(file_name).Worksheets("ms").Range("C" & b + a).Value


If first_item = secound_item And first_item_value = secound_item_value Then


 Application.Workbooks(file_name).Worksheets("ms").Range("A" & a & ":E" & a).Select

With Selection.Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .Color = 49407
    .TintAndShade = 0
    .PatternTintAndShade = 0
End With

 Application.Workbooks(file_name).Worksheets("ms").Range("a" & b + a & ":E" & b + a).Select
With Selection.Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .Color = 49407
    .TintAndShade = 0
    .PatternTintAndShade = 0
End With

End If


Next b
Next a


End Sub

Answer 1

您的技术会反复比较单元格对。以下是避免双重循环的一种方法：

Sub COLOUR_DOUBLE_ENTRY()
   Dim N As Long, wf As WorksheetFunction
   Dim rng As Range, r As Range
   Set wf = Application.WorksheetFunction
   N = Cells(Rows.Count, "A").End(xlUp).Row
   Set rng = Range("A1:A" & N)

   For Each r In rng
      If wf.CountIf(rng, r.Value) > 1 Then
         With r.Interior
             .Pattern = xlSolid
             .PatternColorIndex = xlAutomatic
             .Color = 49407
             .TintAndShade = 0
             .PatternTintAndShade = 0
         End With
      End If
   Next r
End Sub

当然，这只是一种技术描述。你可以根据自己的需要进行调整。