excel - Comparing Cells in a variable number of Columns -

so i've been asked compare data in chart number of columns can variable, 20, i'm told change i'd leave dynamic possible.

the goal make sure of cells in row contain exact same strings, or blank. if there 5 columns, 3 have data "good" , other 2 blank, fine.

the chart start in upper left corner , have both header rows , row names.

i thought compare each cell first cell in each row, can't seem figure out how compare variable cells.

i pretty new using vba , entirely self taught, may on wrong track here.

here's sample code, know i'll need couple of loops working, wanted part working first.

sub row_checker()      dim col_count integer     dim integer       range("a1").select     range(selection, selection.end(xltoright)).select       col_count = application.worksheetfunction.counta(selection)       selection.end(xltoright).select     activecell.offset(1, 1).range("a1").select      = 2 col_count         if ("b" & (activecell.row)) = ("b" & (activecell.row).offset(0, i)   end sub 

i'll make few recommendations more efficient code. i'll out writing loop.

first of all, avoid using select as possible. inefficient , makes code harder read , more break. here, instead of selecting, put value of first column of row in variable called val1 loop through other columns putting values in variable called valn.

since there missing data, used find function find lowest row populated , assigned variable lrow.

for next part, have loop inside of loop. in outer loop iterate through rows, on inner loop iterate through columns. go row, loop through of columns , go next row , same thing.

you can use cells(i,j) move through different cells in loop. powerful part of looping.

you can fill in want happen if cell values match or blank , want if not.

also, sure change sheet1 actual sheet name working with.

see below code:

sub row_checker()      dim col_count integer     dim lrow integer     dim integer     dim j integer     dim val1 string     dim valn string     dim output string        col_count = application.worksheetfunction.counta(rows(1))      lrow = cells.find(what:="*", _                     after:=range("a1"), _                     lookat:=xlpart, _                     lookin:=xlformulas, _                     searchorder:=xlbyrows, _                     searchdirection:=xlprevious, _                     matchcase:=false).row          = 2 lrow             j = 2 col_count             val1 = cells(i, 1).value             valn = cells(i, j).value                 if val1 = valn or valn = empty                 else: cells(i, j).interior.color = 65535                 end if             next j         next  end sub 

congratulations on being self-taught. i'm 100% self-taught in vba , programming. i'm no expert, have learned quite few things along way. i've shared few of things took me long learn. luck , let me know if have more questions.