Differences

This shows you the differences between two versions of the page.

--- microsoft_excel:macros:compare_columns [2021/08/04 14:04] – created peter
+++ microsoft_excel:macros:compare_columns [2021/08/04 15:02] (current) – removed peter
@@ Line 1: / Line 1: @@
-====== Microsoft Excel - Macros - Compare Columns ======
-<code excel>
-Sub CompareColumns()
-    Dim Column1 As Range
-    Dim 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 Then
-      Do Until Column1.Columns.Count = 1
-        MsgBox "You can only select 1 column"
-        Set Column1 = Application.InputBox("Select First Column to Compare", Type:=8)
-      Loop
-    End 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 Then
-      Do Until Column2.Columns.Count = 1
-        MsgBox "You can only select 1 column"
-        Set Column2 = Application.InputBox("Select Second Column to Compare", Type:=8)
-      Loop
-    End If
-  'Check both column ranges are the same size...
-  '---------------------------------------------
-  If Column2.Rows.Count <> Column1.Rows.Count Then
-    Do Until Column2.Rows.Count = Column1.Rows.Count
-      MsgBox "The second column must be the same size as the first"
-      Set Column2 = Application.InputBox("Select Second Column to Compare", Type:=8)
-    Loop
-  End If
-  'If entire columns have been selected (e.g. $AEmbarrassedA), 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 Then
-    Set 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 Long
-  For intCell = 1 To Column1.Rows.Count
-    If Column1.Cells(intCell) <> Column2.Cells(intCell) Then
-      Column1.Cells(intCell).Interior.Color = vbYellow
-      Column2.Cells(intCell).Interior.Color = vbYellow
-    Else
-      Column1.Cells(intCell).Interior.Color = vbWhite
-      Column2.Cells(intCell).Interior.Color = vbWhite
-    End If
-  Next
-End Sub
-</code>