Compare Two Excel sheets and find the difference

0
votes

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

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.

3
vbaexcel

3 Answers

0
votes

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
0
votes

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

-1
votes

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

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.

Code is divided into two subs. 1. Compareworkbook and 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.

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