Excel VBA: Comparing two excel sheets and conditionally formatting based off extra data from sheet 2 -

i'm sort of creating dashboard view have formatting determined other sheets on file.

dashboard have checkbox indicates sheet read from. i'm still working on first sheet.

basically dashboard has names placed in different parts of sheet , second sheet have corresponding names in spreadsheet fashion.

i'm trying write compares range of names list of names on sheet two, if names match, want read number next name on sheet 2 , calculate format name on sheet 1. here sample code

dim processes range dim applications range dim appcount range dim k integer  set process = worksheets("dashboard").range("a4:f17") set applications = worksheets("application").cells(2, 1) set appcount = worksheets("application").cells(2, 2)  k = 0  = 1 process.rows.count     j = 1 process.columns.count         if process.cells(i, j) = applications.offset(k, 0)             if appcount.offset(k, 0) >= 40                 process.cells(i, j).interior.colorindex = 37             else                 if appcount.offset(k, 0) >= 20                     process.cells(i, j).interior.colorindex = 32                 else                     if appcount.offset(k, 0) <= 19                         process.cells(i, j).interior.colorindex = 27                     end if                 end if             end if         end if         k = k + 1     next j next 

basically want match names , compare value in next cell on over sheet 2 determine formatting name on sheet 1.

thank able help. keep getting error when try run this. run-time error '438': object doesn't support property or method.

i propose use conditional formatting, this:

the formulas need following:

=40<iferror(index($ab$1:$ab$2,match($a1,$aa$1:$aa$2,0)),"other") =20<iferror(index($ab$1:$ab$2,match($a1,$aa$1:$aa$2,0)),"other") =0<iferror(index($ab$1:$ab$2,match($a1,$aa$1:$aa$2,0)),"other") 

please note order of rules important, should start 1 starting =40<.