当前位置：theitroad>vba 比较两张 Excel 表格并找出不同之处

vba 比较两张 Excel 表格并找出不同之处

声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow 原文地址: http://stackoverflow.com/questions/41591641/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me): StackOverFlow

提示：将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间：2020-09-12 11:51:51 　来源:igfitidea点击:

Compare Two Excel sheets and find the difference

excelvbaexcel-vba

提问by user2988907

I want to find out or highlight the differences between two Excel sheets.

我想找出或突出显示两个 Excel 工作表之间的差异。

enter image description here

在此处输入图片说明

from above image I want to compare both sheets based on "Name" and "RuleName", if the number matches it needs to check differences for "Text" and "Rule Text" and it needs to find the differences like highlighted text in second Excel document.

从上图我想比较基于“名称”和“规则名称”的两个工作表，如果数字匹配，则需要检查“文本”和“规则文本”的差异，并且需要在第二个 Excel 中找到突出显示的文本等差异文档。

回答by Anand

I have created this file to compare two excel workbooks few years back, code is very elemantary but it does work with few limitations.

几年前我创建了这个文件来比较两个 excel 工作簿，代码非常简单，但它的工作限制很少。

Limitations:

限制：

  1. both file should not have same name
  2. it only compare values in the cell, does not compare any graphics.
  3. It is only comparing first 300 rows and first 200 columns, you can very easily update this in code to fit your need.
  1. 两个文件不应具有相同的名称
  2. 它只比较单元格中的值，不比较任何图形。
  3. 它只比较前 300 行和前 200 列，您可以很容易地在代码中更新它以满足您的需要。

Code is divided into two subs. 1. Compareworkbook and 2. CreateNewWorksheet

代码分为两个子部分。1. Compareworkbook 和 2. CreateNewWorksheet

You can creat browse button macro to populate two excel file names in named cell "file1" and named cell "file2". Then you can use Compareworkbook macro to compare two excel files. Once you run "Compareworkbook" macro, it will create new worksheet to show you the report. it only shows the values which are different.

您可以创建浏览按钮宏来填充命名单元格“file1”和命名单元格“file2”中的两个excel文件名。然后您可以使用 Compareworkbook 宏来比较两个 excel 文件。运行“Compareworkbook”宏后，它将创建新的工作表以向您显示报告。它只显示不同的值。

You can modify this code to compare certain columns or to fit your need. This should give you a good starting point.

您可以修改此代码以比较某些列或满足您的需要。这应该给你一个很好的起点。

Sub CompareWorkbook1()
'this subroutine is created to compare two excel files
'This will only compare first 300 rows and 150 column for all worksheet
'in both workbook assuming both workbook has same number of worksheets

Dim wb As Workbook, wb1 As Workbook, wb2 As Workbook
    Dim ws As Worksheet
    Dim wBook1 As Variant
    Dim wBook2 As Variant
    'Set source workbook
    Set wb = ActiveWorkbook
            'Open the target workbook

wBook1 = ActiveWorkbook.Sheets("Sheet1").Range("file1").Value
wBook2 = ActiveWorkbook.Sheets("Sheet1").Range("file2").Value


    Answer = MsgBox("This will generate a new report, Do you want to proceed?", vbQuestion + vbYesNo, "Are you sure? This will delete existing reports and generate new reports")
    If Answer = vbNo Then
        GoTo exit1
    Else
        If Range("file1").Value = "" Then
            Msg = "ERROR: INFORMATION MISSING ..." & vbNewLine & vbNewLine
            Msg = Msg & "Make sure you browse the file  "
            Msg = Msg & "by clicking on Browse button next to Step 1 " & vbNewLine & vbNewLine
            Msg = Msg & "REPORT WILL NOT GENERATE"
            MsgBox Msg, vbCritical
            GoTo exit1
        End If
        If Range("file2").Value = "" Then
            Msg = "ERROR: INFORMATION MISSING ..." & vbNewLine & vbNewLine
            Msg = Msg & "Make sure you browse the file  "
            Msg = Msg & "by clicking on Browse button next to Step 2 " & vbNewLine & vbNewLine
            Msg = Msg & "REPORT WILL NOT GENERATE"
            MsgBox Msg, vbCritical
            GoTo exit1
        End If


    'generate new worksheet
    ReportName = "Comparison Results"
    Call CreateNewWorksheet(ReportName)

    'set workbooks as variable wb1 and wb2
    Set wb1 = Workbooks.Open(wBook1)
    Set wb2 = Workbooks.Open(wBook2)
wb.Sheets(2).Cells(4, 2).Value = wb1.Name
wb.Sheets(2).Cells(4, 3).Value = wb2.Name
wb.Sheets(2).Cells(3, 7).Value = wb1.Name
wb.Sheets(2).Cells(3, 10).Value = wb2.Name

'Pull data from browsed workbook for All incident
'MsgBox "WOrkbooks are opened"
ThisWorkbook.Activate
Dim oSheet As Excel.Worksheet

'This will populate all Worksheet name in Combo box
    Dim a As Integer
    Dim b As Integer
    Dim c As Integer
    Dim d As Integer
    Dim wSheetsNo As Integer
    Dim wSheetsNo1 As Integer
    Dim wSheetsNo2 As Integer
    a = 1
    b = 1
    c = 1
    d = 1
    wSheetsNo1 = 0
    wSheetsNo2 = 0

    a = 5
    b = 2
    For Each oSheet In wb1.Sheets


        wb.Sheets(2).Cells(a, b) = oSheet.Name
        a = a + 1
        wSheetsNo1 = wSheetsNo1 + 1
    Next oSheet
    a = 5
    b = 3
    For Each oSheet In wb1.Sheets

        wb.Sheets(2).Cells(a, b) = oSheet.Name
        a = a + 1
        wSheetsNo2 = wSheetsNo2 + 1
    Next oSheet

    a = 5
    b = 7
'populates all worksheet from 1st workbook to compare
For wSheetsNo = 1 To wSheetsNo1
    'Compares from row 1 to 300
    For c = 1 To 300
        'Compares columns 1 to 200
        For d = 1 To 200
            'Compares each cell value in each worksheets for these two workbook
            If wb1.Sheets(wSheetsNo).Cells(c, d).Value <> wb2.Sheets(wSheetsNo).Cells(c, d).Value Then
                wb.Sheets(2).Cells(a, b + 1) = "Cells (" & c & ", " & d & ")"
                wb.Sheets(2).Cells(a, b + 4) = "Cells (" & c & ", " & d & ")"
                wb.Sheets(2).Cells(a, b + 2) = wb1.Sheets(wSheetsNo).Cells(c, d).Value
                wb.Sheets(2).Cells(a, b + 5) = wb2.Sheets(wSheetsNo).Cells(c, d).Value
                wb.Sheets(2).Cells(a, b) = wb1.Sheets(wSheetsNo).Name
                wb.Sheets(2).Cells(a, b + 3) = wb2.Sheets(wSheetsNo).Name
                a = a + 1
            End If
        'looks into next column
        Next
    'looks into next row
    Next
'looks into next worksheet
Next

'closes both workbook
wb1.Close
wb2.Close
End If
'exit if files is now browsed or path is empty
exit1:

End Sub

Sub CreateNewWorksheet(ReportName)

Dim wsSheet As Worksheet

    On Error Resume Next
        Set wsSheet = Sheets(ReportName)
    On Error GoTo 0
        If Not wsSheet Is Nothing Then
            Application.DisplayAlerts = False
            Sheets(ReportName).Delete
            Application.DisplayAlerts = True
        End If

'Add New sheet at end of worksheet
    ActiveWorkbook.Sheets.Add After:=Worksheets(Worksheets.Count)

    ActiveSheet.Name = ReportName

Sheets("Comparison Results").Select
    Range("B4").Select
    Sheets("Comparison Results").Select
    Range("B3").Select
    ActiveCell.FormulaR1C1 = "Worksheets which are compared"
    Range("B4").Select
    Columns("B:B").ColumnWidth = 27.57
    Columns("B:B").Select
    Selection.ColumnWidth = 28
    Columns("C:C").Select
    Selection.ColumnWidth = 28
    Range("B3:C3").Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
    End With
    Selection.Font.Bold = True
    Range("B4").Select
    ActiveCell.FormulaR1C1 = "1st Workbook"
    Range("C4").Select
    ActiveCell.FormulaR1C1 = "2nd Workbook"
    Range("B4:C4").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("B3:C50").Select
    ActiveWindow.SmallScroll Down:=-45
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Range("B3:C4").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 10092543
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Range("B5:C50").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 10092543
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Range("C13").Select
    ActiveWindow.SmallScroll Down:=-15
    Range("B3:C3").Select
    ActiveCell.FormulaR1C1 = "Worksheets which are compared"
    Columns("G:L").Select
    Selection.ColumnWidth = 28
    Selection.ColumnWidth = 10
    Selection.ColumnWidth = 15
    Selection.ColumnWidth = 18
    Range("G3:I3").Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
    End With
    Selection.Copy
    Range("J3").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    Range("G3:I3").Select
    ActiveCell.FormulaR1C1 = ""
    Range("G4").Select
    ActiveCell.FormulaR1C1 = "Worksheet"
    Range("H4").Select
    ActiveCell.FormulaR1C1 = "Cell number"
    Range("I4").Select
    ActiveCell.FormulaR1C1 = "Value in the cell"
    Range("G4:I4").Select
    Selection.Copy
    Range("J4").Select
    ActiveSheet.Paste
    Range("B4").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    Range("C4").Select
    Selection.ClearContents
    Range("G3:L10000").Select
    Range(Selection, Selection.End(xlDown)).Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 10092543
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Range("G3:L4").Select
    Selection.Font.Bold = True
    Range("B3:C4").Select
    Selection.Font.Bold = False
    Selection.Font.Bold = True
    Range("B4:L4").Select
    Range(Selection, Selection.End(xlDown)).Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("G3:L3").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
    End With
    Range("G5").Select
    ActiveWindow.SmallScroll Down:=-15
    Range("G3:I3").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlToLeft)).Select
    Range("G3:L3").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Range("G3:L4").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
     Range("G3:L10000").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With


    Range("B3:C4").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Range("B3:C50").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Range("G3:I10000").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
Columns("D:F").Select
    Range("F1").Activate
    Selection.ColumnWidth = 3
    Range("G2:L2").Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
    End With
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 10092543
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Range("G2:L2").Select
    ActiveCell.FormulaR1C1 = "Comparison Results"
    Range("G2:L2").Select
    Selection.Font.Bold = True
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
    End With
    With Selection.Font
        .Name = "Calibri"
        .Size = 14
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontMinor
    End With
    Range("G2:L2").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    'Sheets("Sheet1").Select
    Range("B2").Select
Range("G3:L4").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Range("B1").Select
End Sub

回答by mlm0b11011

This should do:

这应该做： 

Sub HighlightDiffBtwSheets()
'Substitute "TEST1" with the name of the sheet where you have the Name-Text columns
'Substitute "TEST2" with the name of the sheet where you have the RuleName-RuleText columns
'Substitute A in the Range with the column letter of Name/RuleName

For Each Name In Sheets("TEST1").Range("A2:A" & Sheets("TEST1").Cells(Rows.Count, 1).End(xlUp).Row)
        For Each RuleName In Sheets("TEST2").Range("A2:A" & Sheets("TEST2").Cells(Rows.Count, 1).End(xlUp).Row)
        If InStr(RuleName.Value, Name.Value) <> 0 Then
            If Name.Offset(, 1).Value <> RuleName.Offset(, 1).Value Then
             RuleName.Offset(, 1).Select
             With Selection.Interior
            .Color = 65535
             End With

             End If
        End If
        Next
Next
End Sub

回答by ProfessorPorcupine

An easier non VBA way to do this is to use Conditional Formatting. Just Create A New Rule, then select Use Formula option. Use a relative reference (no dollar signs) and copy to where you need it. For example, =A1<>Sheet1!A1

一种更简单的非 VBA 方法是使用条件格式。只需创建一个新规则，然后选择使用公式选项。使用相对引用（无美元符号）并复制到您需要的位置。例如，=A1<>Sheet1!A1

相关推荐

SQL Excel VBA 运行时错误 3709 无效连接

SQL Excel VBA 运行时错误 3709 无效连接

过滤不同列中的多个条件，包括 VBA 中的字符串和日期

过滤不同列中的多个条件，包括 VBA 中的字符串和日期

从范围 VBA 中查找最后一个单元格

从范围 VBA 中查找最后一个单元格

VBA：使用 VBA 从 Excel 图表中编辑图例名称

VBA：使用 VBA 从 Excel 图表中编辑图例名称

相关推荐
最近更新
标签