Check which rows a formula refers to in Excel (error checking with VBA) -

is there way use vba list rows formula refers to?

i have built spreadsheet should refer cells in same row, , formulas referring different row in error. cannot work out how in vba. example, third formula in error.

=(d3+f3)/(e3+d3) =d4/e4 =d5^e5+f12 =d6+f6^g6 

ok, can understand reason want check if formula refer single row only...

here's wrote. please, read comments in code.

option explicit  sub checkformulas() dim c range dim wsh worksheet dim sformula string  'context set wsh = thisworkbook.worksheets(1) 'loop through set of cells in usedrange each c in wsh.usedrange     'formula starts "="     if c.formula "=*"         sformula = c.formula         'check if formula refer single row         'if not, change background color form cell         if doesformularefertosinglerow(sformula)             c.interior.color = vbred         end if     end if next  end sub  'needs reference ms vbscript regular expression 5.5 '           ms scripting runtime function doesformularefertosinglerow(sformulatocheck string) boolean dim bretval boolean, spattern string dim re vbscript_regexp_55.regexp, mc vbscript_regexp_55.matchcollection, m vbscript_regexp_55.match dim odict scripting.dictionary  bretval = false  spattern = "\d{1,}"  set re = new vbscript_regexp_55.regexp re     .global = true     .multiline = false     .pattern = spattern     set mc = .execute(sformulatocheck) end  set odict = new scripting.dictionary  each m in mc     if not odict.exists(m.value)         odict.add m.value, m.length     end if next  bretval = not (odict.count = 1)  exit_doesformularefertosinglerow     on error resume next     set m = nothing     set mc = nothing     set re = nothing     set odict = nothing     doesformularefertosinglerow = bretval     exit function  err_doesformularefertosinglerow:     msgbox err.description, vbexclamation, err.number     resume exit_doesformularefertosinglerow end function 

as can see, used regex , dictionary object. not formget add references.

for further details, above objects, please see:
regex - syntax
creating regular expression
microsoft beefs vbscript regular expressions