Comparing two columns on Excel VBA -

i'm looking compare 2 columns in excel using vba. i'm using below code, taking ages because there thousands of cells. i'm looking put maximum limit don't know how/where apply that. don't know if knows of more efficient way of doing code?

private sub commandbutton1_click() dim column1 range dim column2 range  'prompt user first column range compare... set column1 = application.inputbox("select first column compare", type:=8)  'check range have provided consists of 1 column... if column1.columns.count > 1     until column1.columns.count = 1         msgbox "you can select 1 column"         set column1 = application.inputbox("select first column compare", type:=8)     loop end if  'prompt user second column range compare... set column2 = application.inputbox("select second column compare", type:=8)  'check range have provided consists of 1 column... if column2.columns.count > 1     until column2.columns.count = 1         msgbox "you can select 1 column"         set column2 = application.inputbox("select second column compare", type:=8)     loop end if  'check both column ranges same size... if column2.rows.count <> column1.rows.count     until column2.rows.count = column1.rows.count         msgbox "the second column must same size first"         set column2 = application.inputbox("select second column compare", type:=8)     loop end if  'if entire columns have been selected, limit range sizes if column1.rows.count = 11600     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 comparison , set cells same yellow dim intcell long intcell = 1 column1.rows.count     if column1.cells(intcell) = column2.cells(intcell)         column1.cells(intcell).interior.color = vbyellow         column2.cells(intcell).interior.color = vbyellow     end if next end sub 

thanks.

i may suggest couple of tweaks help.

1. disable screen update while comparison loop running. can with:

   application.screenupdating = false  'your loop here' application.screenupdating = true

2. use variables expressions repeat through code,

   column1.rows.count

i haven't test it, should pretty fast check out ;)