other options to functions which cannot modify contents?
 

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

other options to functions which cannot modify contents?
 

Chip is right. A function can't do much beyond returning a value. It can't
change formats.


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