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

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

i'd imagine must easy do, can't find help googlefu, , don't understand vba's object reference guide thing.

what i'm trying create macro allows me select column, have each cell in column compared - row row - cell in column right (e.g. a1:b1 if a1 selected, f1:g1 f1 selected). don't want cell compared cells in different column (e.g. a1 compared cells in column b), or cells in same row (e.g. a1 compared cells in row a).

when manually, highlight 2 cells want at, click "conditional formatting", formatting formula, , come in =x1<>y1 (where x1 column i'd select, e.g. a1 or f1, , y1 column want compare to, e.g. b1 or g1).

i tried using macro recorder, it's forcing me specify columns id instead of allowing me take column selection mouse. if there's way create textbox pop lets me type in columns want, work. work if there's way populate selected column x1 , populate column right y1. haven't been able find info on either of possibilities. know .offset property exists, don't know how used want.

here's code came 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 selection.formatconditions(1).interior .patterncolorindex = xlautomatic .color = 4145151 .tintandshade = 0 end selection.formatconditions(1).stopiftrue = false end sub

here's code goes row-by-row (found @ excelforum), compares cells within row other cells within row:

sub colorduplicates() dim info variant dim dso object dim rng range 'assumes row 1 has column headers set rng = range("a2").currentregion.offset(1, 0) info = rng.value set dso = createobject("scripting.dictionary") = 1 ubound(data, 1) j = 1 ubound(data, 2) key = trim(data(i, j)) if key <> "" if not dso.exists(key) dso.add key, 1 else rng.cells(i, j).interior.colorindex = 3 end if end if next j dso.removeall next set dso = nil end sub

if there's way create textbox pop lets me type in columns want, work.

fortunately, there is:

dim rng range set rng = application.inputbox("select column(s)", type:=8)

alternatively:

i tried using macro recorder, it's forcing me specify columns id instead of allowing me take column selection mouse.

change this:

columns("x:y").select range("y1").activate

to (which select first cell in sec column of current selection):

range(selection.cells(1, 2).address).activate

excel vba excel-vba