How to Compare Columns With VBA

1. Click the 'Developer' tab in Excel, and then click 'Visual Basic.'
2. Click 'Insert.' Click 'Module' to open a new module window.
3. Cut and paste the following code into the window:Sub CompareColumns()Dim Column1 As RangeDim Column2 As Range'Prompt user for the first column range to compare...'----------------------------------------------------Set Column1 = Application.InputBox('Select First Column to Compare', Type:=8)'Check that the range they have provided consists of only 1 column...If Column1.Columns.Count > 1 ThenDo Until Column1.Columns.Count = 1MsgBox 'You can only select 1 column'Set Column1 = Application.InputBox('Select First Column to Compare', Type:=8)LoopEnd If'Prompt user for the second column range to compare...'----------------------------------------------------Set Column2 = Application.InputBox('Select Second Column to Compare', Type:=8)'Check that the range they have provided consists of only 1 column...If Column2.Columns.Count > 1 ThenDo Until Column2.Columns.Count = 1MsgBox 'You can only select 1 column'Set Column2 = Application.InputBox('Select Second Column to Compare', Type:=8)LoopEnd If'Check both column ranges are the same size...'---------------------------------------------If Column2.Rows.Count
Column1.Rows.Count ThenDo Until Column2.Rows.Count = Column1.Rows.CountMsgBox 'The second column must be the same size as the first'Set Column2 = Application.InputBox('Select Second Column to Compare', Type:=8)LoopEnd If'If entire columns have been selected (e.g. $AA), limit the range sizes to the'UsedRange of the active sheet. This stops the routine checking the entire sheet'unnecessarily.'-------------------------------------------------------------------------------If Column1.Rows.Count = 65536 ThenSet Column1 = Range(Column1.Cells(1), Column1.Cells(ActiveSheet.UsedRange.Rows.Count))Set Column2 = Range(Column2.Cells(1), Column2.Cells(ActiveSheet.UsedRange.Rows.Count))End If'Perform the comparison and set cells that are the same to yellow'----------------------------------------------------------------Dim intCell As LongFor intCell = 1 To Column1.Rows.CountIf Column1.Cells(intCell) = Column2.Cells(intCell) ThenColumn1.Cells(intCell).Interior.Color = vbYellowColumn2.Cells(intCell).Interior.Color = vbYellowEnd IfNextEnd Sub
4. Press 'F5' to run the routine and compare the columns.