CODEWITHSUNDEEP
vbaexcel
ProlificAxons
ProlificAxons

Reputation: 23

Excel macro to compare selected column cell to "next column" cell and color-fill if identical

I'd imagine that this must be very easy to do, but I simply can't find help with my GoogleFu, and I don't understand VBA's object reference guide thing.

What I'm trying to do is make a macro that allows me to select a column, then have each cell in that column compared - row by row - to the cell in the column to its right (e.g. A1:B1 if A1 is selected, F1:G1 is F1 is selected). I don't want a cell compared to all cells in a different column (e.g. A1 compared to all cells in column B), or to all cells in the same row (e.g. A1 compared to all cells in row A).

When I do this manually, I highlight two cells that I want to look at, click "Conditional formatting", do formatting by formula, and enter =x1<>y1 (where x1 is the column I'd select, e.g. A1 or F1, and y1 is the column I want to compare to, e.g. B1 or G1).

I tried using the macro recorder, but it's forcing me to specify columns by ID instead of allowing me to choose a column by selection with my mouse. If there's a way to make a textbox pop up that lets me type in the columns I want, that would work. It would also work if there's a way to populate the selected column as x1 and populate the column to its right as y1. I haven't been able to find info on either of those possibilities. I know that the .Offset property exists, but I don't know how it could be used for what I want.

Here's the code that came from my macro recorder:

    Sub ColorHighlightDiscrepancies()
'
' ColorHighlightDiscrepancies Macro
' ColorHighlightDiscrepancies
'

'
    Columns("X:Y").Select
    Range("Y1").Activate
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=X1<>Y1"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 4145151
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
End Sub

Here's code that goes row-by-row (found at ExcelForum), but compares all cells within a row to all other cells within the row:

Sub ColorDuplicates()

  Dim Data As Variant
  Dim DSO As Object
  Dim Rng As Range

   'Assumes Row 1 has Column Headers
    Set Rng = Range("A2").CurrentRegion.Offset(1, 0)
    Data = Rng.Value

    Set DSO = CreateObject("Scripting.Dictionary")

      For I = 1 To UBound(Data, 1)
        For J = 1 To UBound(Data, 2)
          Key = Trim(Data(I, J))
          If Key <> "" Then
            If Not DSO.Exists(Key) Then
               DSO.Add Key, 1
            Else
               Rng.Cells(I, J).Interior.ColorIndex = 3
            End If
          End If
        Next J
        DSO.RemoveAll
      Next I

    Set DSO = Nothing

End Sub

Upvotes: 0

Views: 819

Answers (1)

David Zemens
David Zemens

Reputation: 53663

If there's a way to make a textbox pop up that lets me type in the columns I want, that would work.

Fortunately, there is:

Dim rng as Range
Set rng = Application.InputBox("Select column(s)", Type:=8)

Alternatively:

I tried using the macro recorder, but it's forcing me to specify columns by ID instead of allowing me to choose a column by selection with my mouse.

Change this:

Columns("X:Y").Select
Range("Y1").Activate

To this (which will select the first cell in the second column of the current Selection):

Range(Selection.Cells(1, 2).Address).Activate

Upvotes: 1

Related Questions