Comapre two worksheet with multiple column dynamically excel vba

In this lession We will learn How to compare two worksheet dynamically with multiple column.

Here I will insert different size datatable into sheet1 & sheet2. I will use checkboxes to compare data.

I want that when I clicked on CheckBox1 then It will compare data from sheet1 by sheet2 & highlight by yellow color.

VBA ColorIndex Code List...

VBA ColorIndex Examples

Set Cell Background Color

        
                    Range("A1").Interior.ColorIndex = 6

Set Cell Font Color

        
                    Range("A1").Font.ColorIndex = 5

Set Cell Borders Color

        
                    Range("A1").Borders.ColorIndex = 5

Get Cell Background ColorIndex

        
                    Dim col as integer
                  
                    col = Range("A1").Interior.ColorIndex

Set a Cell Background Color to Another Cell’s Color

        
                    Range("A1").Interior.ColorIndex  = Range("B1").Interior.ColorIndex

VBA Codes thats are used in this tutorials...

Private Sub CheckBox1_Click()
    Dim sh, wsh As Worksheet
      Set sh = Sheets("Datasheet1")
      Set wsh = Sheets("Datasheet2")
    Dim x As Long
      x = sh.Range("A" & Rows.Count).End(xlUp).Row
    
    Dim myRange As Range
      Set myRange = sh.Range("A2", "A" & x)
    Dim p, i As Long
      p = wsh.Range("G" & Rows.Count).End(xlUp).Row
    Dim b As Integer
      b = wsh.Range("B6").Value

    For i = 2 To p
     If Me.CheckBox1 = True Then
      If Application.WorksheetFunction.CountIf(myRange, wsh.Cells(i, b)) > 0 Then
       wsh.Cells(i, 7).Interior.ColorIndex = 6 ' 6 for yellow color
      End If
     Else
       wsh.Cells(i, 7).Interior.ColorIndex = 2 ' 2 for white color
     End If
    Next i
End Sub

Codes Discussion :

declare two variables to set the worsheets

Declare a variable as long to find out last row number. Here I declared the variables by x and used it to find out the last non-blank row number based on Column A

Declare another variable to input column number whose cells value will be compared

use a loop function with a variables to check each and every cell value

Now use countif function to to find out duplicate values. and after finding duplicate value then change the background color for those cell only which are duplicate..

 
  If Application.WorksheetFunction.CountIf(myRange, wsh.Cells(i, b)) > 0 Then

VBA codes for CheckBox2.....

 
 Private Sub CheckBox2_Click()
    Dim sh, wsh As Worksheet
    Set sh = Sheets("Datasheet1")
    Set wsh = Sheets("Datasheet2")
    Dim x As Long
    x = sh.Range("A" & Rows.Count).End(xlUp).Row
    
    Dim myRange As Range
    Set myRange = sh.Range("B2", "B" & x) ' change here
    Dim p, i As Long
    p = wsh.Range("G" & Rows.Count).End(xlUp).Row
    Dim b As Integer
    b = wsh.Range("C6").Value ' chnage here
    
     For i = 2 To p
     If Me.CheckBox2 = True Then
     If Application.WorksheetFunction.CountIf(myRange, wsh.Cells(i, b)) > 0 Then
     wsh.Cells(i, b).Interior.ColorIndex = 6 ' 6 for yellow color
     End If
     Else
     wsh.Cells(i, b).Interior.ColorIndex = 2 ' 2 for white color
     End If
     Next i
End Sub

VBA codes for CheckBox3..

 
 Private Sub CheckBox3_Click()
Dim sh, wsh As Worksheet
Set sh = Sheets("Datasheet1")
Set wsh = Sheets("Datasheet2")
Dim x As Long
x = sh.Range("A" & Rows.Count).End(xlUp).Row

Dim myRange As Range
Set myRange = sh.Range("AB2", "AB" & x) ' change here
Dim p, i As Long
p = wsh.Range("G" & Rows.Count).End(xlUp).Row
Dim b As Integer
b = wsh.Range("D6").Value ' chnage here

 For i = 2 To p
 If Me.CheckBox3 = True Then
 If Application.WorksheetFunction.CountIf(myRange, wsh.Cells(i, b)) > 0 Then
 wsh.Cells(i, b).Interior.ColorIndex = 6 ' 6 for yellow color
 End If
 Else
 wsh.Cells(i, b).Interior.ColorIndex = 2 ' 2 for white color
 End If
 Next i
End Sub

VBA Codes for CheckBox4 Where I compare all data for sheet1 with sheet2's particular cell values..

 
  Private Sub CheckBox4_Click()

Dim sh, wsh As Worksheet
Set sh = Sheets("Datasheet1")
Set wsh = Sheets("Datasheet2")
Dim x As Long
x = sh.Range("A" & Rows.Count).End(xlUp).Row

Dim myRange As Range
Set myRange = sh.UsedRange ' change here
Dim p, i As Long
p = wsh.Range("G" & Rows.Count).End(xlUp).Row
Dim b As Integer
b = wsh.Range("E6").Value ' chnage here

 For i = 2 To p
 If Me.CheckBox4 = True Then
 If Application.WorksheetFunction.CountIf(myRange, wsh.Cells(i, b)) > 0 Then
 wsh.Cells(i, b).Interior.ColorIndex = 6 ' 6 for yellow color
 End If
 Else
 wsh.Cells(i, b).Interior.ColorIndex = 2 ' 2 for white color
 End If
 Next i
End Sub

Click here to download the file View more videos & Subscribe my Channel Please if didn't

Nsutradhar - Excel Tutorials