Color contents of a cell accessed by a function?
 

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

Color contents of a cell accessed by a function?
 

A function can only return a result.
Outside very few exceptions, it cannot alter the look or contents of another
cell.

HTH
--
AP

"James Cornthwaite" a écrit dans le
message de news: ...
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

Color contents of a cell accessed by a function?
 

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

Color contents of a cell accessed by a function?
 

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

Color contents of a cell accessed by a function?
 

without knowing what you are trying to achieve:

In a general module:

Public v(1 To 65536) As String
Public idex As Integer


Function FindOldNominal(NomCode, Rng1 As Range)
Dim Rng As Range, s As String
Dim bFnd As Boolean
Set Rng = Application.Caller
s = Rng.Address(0, 0, xlA1, True)
bFnd = False
If idex 0 Then
For i = 1 To 65536
If v(i) = s Then
bFnd = True
Exit For
ElseIf IsEmpty(v(i)) Then
Exit For
End If
Next
If Not bFnd Then
idex = idex + 1
v(idex) = s
End If
Else
idex = idex + 1
v(idex) = s
End If

FindOldNominal = WorksheetFunction.VLookup(NomCode, Range,5,false)
End Function

In the worksheet module:

Private Sub Worksheet_Calculate()
For i = 1 To idex
If Len(Trim(v(i))) 0 Then _
Evaluate(v(i)).Interior.ColorIndex = 3
Next
End Sub

--
Regards,
Tom Ogilvy

"James Cornthwaite" wrote in message
...
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