Accessing a variable(a range) and modifying it
 

I have written the macro as below

Function FindOldNominal(NomCode, Range)

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


My question is what code can I insert that will change the colour (say) of
the cell accessed by this function.
My aim is to show all the cells accessed (and contents returned) by changing
their colour so I know which cells in my table have not yet been looked at.

Thanks
James



"TOM REPLIED THE FOLLOWING"
Nothing.

from this context, A function can do nothing more than returning a value to
the cell in which it is located - just like the built in functions.

You could possibly have each function update a defined name or public
variable or something and then use the calculate event to access that list
and color the cells.

--
Regards,
Tom Ogilvy


SO IF I HAVE A DEFINED RANGE "NAME" SAY, WHAT CODE DO I NEED IN THE FUNCTION
TO ACCESS AND UPDATE A CELL IN THE RANGE (i'd like to change the colour of
any accessed cells to red, which were found in the lookup)
Many thank James

Accessing a variable(a range) and modifying it
 

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:

I have written the macro as below

Function FindOldNominal(NomCode, Range)

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


My question is what code can I insert that will change the colour (say) of
the cell accessed by this function.
My aim is to show all the cells accessed (and contents returned) by changing
their colour so I know which cells in my table have not yet been looked at.

Thanks
James



"TOM REPLIED THE FOLLOWING"
Nothing.

from this context, A function can do nothing more than returning a value to
the cell in which it is located - just like the built in functions.

You could possibly have each function update a defined name or public
variable or something and then use the calculate event to access that list
and color the cells.

--
Regards,
Tom Ogilvy


SO IF I HAVE A DEFINED RANGE "NAME" SAY, WHAT CODE DO I NEED IN THE FUNCTION
TO ACCESS AND UPDATE A CELL IN THE RANGE (i'd like to change the colour of
any accessed cells to red, which were found in the lookup)
Many thank James