Excel 2007 : Creating a Compare Macro

[devildog53]

I'm trying to create a macro that will allow the user to compare any two columns in a spreadsheet. So, they would click on the macro to run and a pop up would come up asking which two columns they would like to compare. I've searched around and haven't found another example of this. I know it can be done in Access, but can it be done in Excel too? My leader at work seems to think this would be easier than going through the function menu in order to accomplish this.

Thanks!

[JBeaucaire]

My leader at work seems to think this would be easier than going through the function menu in order to accomplish this.

Ask your leader why they think this, what do they have in mind?

What is the expected results of your macro?

[MarvinP]

Hi devildog53 and welcome to the forum,

Creating VBA code to ask which two columns are to be compared increased the complexity of the problem a lot. Find an example that compares two columns and returns True or False depending on if all the row values match exactly. After looking at the answer, change a value in one of the columns to ensure it works. Then read about Array Formulas and see how complex this could become.

http://www.cpearson.com/excel/ArrayFormulas.aspx
http://www.ozgrid.com/Excel/arrays.htm

[devildog53]

Thank you both for your help.

JBeaucaire,

They just think it would be easier to have a popup ask which columns to compare as opposed to going through the function menu. A lot of the people on my team do not possess a great amount of Excel skills. The expected results of my macro is to have a popup come up asking which rows to compare, after inputting the rows the macro will then use the exact statement to compare the two columns one row at a time (probably safest in the final column). So, my final output would have the exact statement in every cell in column XFD. So in XFD1, it's comparing the data in A1 to B1 and returning a True/False statement. In XFD2, it's comparing A2 and B2, and so on. Every cell in XFD would be comparing its comparable cells in the same row. Sorry if this isn't too clear.

Marvin,

Thank you for your input. We have discussed the possibility of the user actually editting the vba code that the macro creates, but I was wondering if there's any way that an input can be created to do this instead.

[devildog53]

Here's what I have so far. I just don't know the syntax to substitute my entered values into the string highlighted in red.

Sub Test2()
'
' Test2 Macro
  Dim Compare1 As String
  Dim Compare2 As String
'
  Compare1 = InputBox("First Column to Compare")
  Compare2 = InputBox("Second Column to Compare")
    Range("XFD1").Select
    ActiveCell.FormulaR1C1 = "=EXACT(Compare11,Compare21])"
    Range("XFD1").Select
    Selection.Copy
    Range("XFD2").Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.Paste
End Sub

[JBeaucaire]

As per forum rules, you need to edit that post above and add CODE tags around the code like I'll show here (demonstrated in my signature as well).

Try this:

Option Explicit

Sub CompareColumns()
Dim Col1 As Range, Col2 As Range, LR1 As Long, LR2 As Long

On Error Resume Next

Col1 = Application.InputBox("Click any cell in first column", "Select", Type:=8).Address
Set Col1 = ActiveCell
Col2 = Application.InputBox("Click any cell in second column", "Select", Type:=8).Address
Set Col2 = ActiveCell

If Col1.Column = Col2.Column Then
    MsgBox "Cannot compare a column to itself"
    Exit Sub
Else
    LR1 = Cells(Rows.Count, Col1.Column).End(xlUp).Row
    LR2 = Cells(Rows.Count, Col2.Column).End(xlUp).Row
    
    LR1 = Application.WorksheetFunction.Max(LR1, LR2)
    
    With Range("XFD1:XFD" & LR1)
        .FormulaR1C1 = "=EXACT(RC" & Col1.Column & ",RC" & Col2.Column & ")"
        .Value = .Value
    End With
    
    MsgBox "Done"
End If
End Sub

[devildog53]

Sorry for the delayed response. I got busy with the holidays.

I tried to run your last code and received the "cannot compare a column with itself" error every time I tried to run it. So, I'm not sure if it works or not.

I've also added some formatting into the coding that I've done. Here's my latest code. If I could only substitute the fields I input into the exact statement then I'd be set. But I don't know how to do that.

Sub CompareColumns()
'
' CompareColumns Macro
'
  Dim Compare1 As String
  Dim Compare2 As String
'
    Compare1 = InputBox("First Cell in Column to Compare")
    Compare2 = InputBox("Cell in Second Column to Compare Against")
    Range("XFD1").Select
    ActiveCell.FormulaR1C1 = "=EXACT(Compare1,Compare2)"
    Range("XFD1").Select
    Selection.Copy
    Range("XFD2").Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.Paste
    Selection.End(xlUp).Select
    Columns("XFD:XFD").Select
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
        Formula1:="=TRUE"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent3
        .TintAndShade = 0.599963377788629
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
        Formula1:="=FALSE"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent6
        .TintAndShade = 0.599963377788629
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Range("XFB12").Select
End Sub