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" <chip@cpearson.com> wrote in message
> news:eAbRWH$hGHA.4284@TK2MSFTNGP05.phx.gbl...
> >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" <jamescornthwaite@btinternet.com> wrote in message
> > news:2LKdnTMWpcwdmR7ZRVnyhA@bt.com...
> >> 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
> >>
> >>
> >
> >
>
>
>