Check Cell Formatting in Excel -

i'm trying produce simple excel assessment , have come across stumbling block.

the 'candidate' needs correctly format cell 'currency' shows £ sign in cell.

is there function can check presence of '£' after cell has been formatted

the function bar shows value number without £ sign

on scoring sheet, give value of '1' correct answer , '0' incorrect.

i therefore use advice me establish if 'candidate' correctly formats cell

many thanks

paul

go vbe , add empty module. copy module following code:

option explicit  public function isformatted(formattedcell range) byte  isformatted = 0 if instr(1, formattedcell.cells(1, 1).numberformat, "$", vbtextcompare) >= 1 isformatted = 1  end function 

then have function can use this: =isformatted(d2).

note: generic number format currencies made $ sign , not £. translates automatically uk currency based on windows settings. if want make sure people have set format of cell uk £ need change following line in above code:

if instr(1, formattedcell.cells(1, 1).numberformat, "[$£-809]", vbtextcompare) >= 1 isformatted = 1 

please keep in mind above function not volatile. means value of function not change if referenced cell changed. in other words: if check cell =isformatted(d2) , assume formula in cell f2 f2 0 when d2 not correctly formatted. now, if format d2 correctly f2 still display 0. so, you'll have force-update sheet functions f9 or add application.volatile above function. yet, may slow down excel. @ following article more details: http://www.excel-easy.com/vba/examples/volatile-functions.html