Excel VBA Compare cell value to list and overwrite value in separate sheet -

in workbook have, users either manually enter account code or select 1 list , account codes placed in column c (c7:c446) in sheet called "je". account codes ####### - ### - ## - ######. in column d (d7:d446) in sheet "je", there formula captures last 6 digits of account code. in sheet called "required_refs", there list of 6 digit codes in column a. if value in d column in sheet "je" equals of values in column of "required_refs" sheet, value in d column cell overwrite cell value in cell d1 in separate sheet called "references" (i know may have been confusing, sorry)

example: if value of d25 matches of values listed in column of sheet "required_refs", upon double clicking red colored f25 cell, put value of d25 (of sheet "je"), , put in cell d1 on sheet "references".

i've taken crack @ best know how. i've placed code in sheet je:

private sub worksheet_beforedoubleclick(byval target range, cancel boolean)      dim project range: set project = range("d7:d446")     dim param range:    set param = worksheets("references").range("d1").value       each cell in project         if project.value = worksheets("required_refs").range("a:a").value         call gotoref_ 'macro selects/navigates required_ref sheet         project.value = param         end if  end sub 

thanks in advance suggestions on how complete this. can elaborate on further if needed.

this want:

private sub worksheet_beforedoubleclick(byval target range, cancel boolean)    if intersect(target, range("f7:f446")) nothing exit sub    dim varreference variant    varreference = columns("d").cells(target.row).value2   if not iserror(application.match(varreference, worksheets("required_refs").columns("a"), 0))     worksheets("references").range("d1").value = varreference   end if  end sub 

important points:

whenever working event handlers, always limit scope of target range in first line. otherwise, might not work correctly or slow done spreadsheet.

make sure je sheet column d values , required_refs sheet column a values all either text or numbers. otherwise values won't compared correctly.

note usage of application.match() instead of worksheetfunction.match() access worksheet function. this, coupled use of variant type variable, allows trap error occurs if match fails.