Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Modify cell contents | Excel Discussion (Misc queries) | |||
How do I Modify The XML View Options | New Users to Excel | |||
Cannot modify Excel Tools Options | Excel Discussion (Misc queries) | |||
Macro Button To Modify A Cell's Contents? | Excel Programming | |||
How to modify contents of TextFrame? | Excel Programming |