The reason it is showing up as #REF is because there is no corresponding
value in your vlookup table.
Thre reason it is not formatting as a color is because #REF is not a number.

I would suggest you look at the value you are looking up and see if it is in
the lookup table. That might help...

Anna

"Louise" wrote:

I have produced a custom number format that will format positive numbers in
blue, negative numbers in red, it enters zero values as 'n/a' and text in
green.

However, I have applied this number format to a worksheet which contains a
vlookup. One of the cells in my lookup has returned #REF so obviously my
formula is wrong somewhere - this is fine, I can work that one out.

What I cannot understand - the text #REF doesn't appear in any of the
colours I specified in the custom number, so what has it been created as??
It can't be a number because it would appear in blue and if it was classed as
zero, it would appear in green?? The rest of my vlookup has appeared in the
format I specified.

I'm baffled. Can anybody shed any light??

Thank you.

Louise