Are you using this UDF on a worksheet?

Do you pass the NomCode as a range?
Kind of:
=findoldnominal(a1,sheet2!a:e)

Are you trying to add the comment to the NomCode cell?

If yes, then maybe...

Option Explicit
Function FindOldNominal(NomCode As Range, definedRange As Range) As Variant

Dim res As Variant 'could be an error

res = Application.VLookup(NomCode, definedRange, 5, False)
If IsError(res) Then
FindOldNominal = "Not Found"
Else
FindOldNominal = res
End If

On Error Resume Next
NomCode.Comment.Delete
On Error GoTo 0

NomCode.AddComment Text:="accesses"

End Function

There are some "interesting" things about the way worksheetfunction.vlookup()
works in comparison to application.vlookup().

I chose to use application.vlookup() to make my life simpler.

If this doesn't do what you want and you can't modify it, post back with a few
more details. I'm sure you'll get some responses.

James Cornthwaite wrote:

So then using comments to mark accessed cells (as this is the only option
with functions since they are not able to modifying cell formats in other
ways)

Function FindOldNominal(NomCode, definedRange)

FindOldNominal = WorksheetFunction.VLookup(NomCode, definedRange, 5, False)

'(now attempt to mark the accessed cell - the bit not yet working!!!!)

Dim rng As Range
Dim noOfRows As Integer

noOfRows = Match(NomCode, definedRange, 0)
rng = Offset(A1, noOfRows, 0, 1, 1)
rng.AddComment ("accesses")

EndFunction

I realise the above is probably incorrect syntax, but have only just started
to learn macro and excel functions (not even sure functions are found in
what classes etc).

Please could you ammend the above so it works. Hopefully the gist of what
i'm trying is clearer enough.
Many many thanks in anticipation
James

PREVIOUS POST

""Gary replied..............

A function can deposit comments in cells:

Function demo(r As Range) As Integer
demo = 1
If r.Comment Is Nothing Then
Else
r.Comment.Delete
End If
r.AddComment Text:="marked"
End Function

This dumb little macro just returns 1, but it marks its reference with a
comment

=demo(A1) results in A1 getting a comment. So even if you can't color A1,
you can mark it.
--
Gary's Student

"James Cornthwaite" wrote:

o right, never straight forwards is it.

Is there any other way around the problem, to achieve the same
goal.??????????

Thanks
James
"Chip Pearson" wrote in message
...
A function called from a worksheet cell can only return a value to the
calling cell. It cannot change the value or format of any cell.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"James Cornthwaite" wrote in message
...
Ideally i would like to color the contents of the cell accessed by the
call of my function "findnewnominal(...)" The reason for this is the
function is called many times on a range table range and I would like
to
know at a glance which rows in the table hadn't yet been accessed by a
calls of the function.

My macro to date (which works fine is)


Function FindOldNominal(NomCode, definedRange)

FindOldNominal = Worksheetfunction.VLookup(NomCode, definedRange, 5,
false)
EndFunction





but if i want to color the cell accessed I imagine i have to use
something like the following? (my attempt of something pseudocode)


Function FindOldNominal(NomCode, definedRange)

Activecell = reference(Worksheetfunction.VLookup(NomCode, definedRange,
5, false))
Activecell.Interior.ColorIndex = 3 (i.e red etc)

FindOldNominal = Worksheetfunction.VLookup(NomCode, definedRange, 5,
false)
EndFunction


I appreciate the above is probably non sensical but know little about
excel functions and just wanted to make as clear as possible what i
wanted to do.
Any help would be greatly appreciated.

Thanks
James








--

Dave Peterson