Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Modifying the Range Nameange the names | Excel Discussion (Misc queries) | |||
Accessing specific items in a range | Excel Programming | |||
setting a range variable equal to the value of a string variable | Excel Programming | |||
VLookup error message while accessing range in closed workbook. | Excel Programming | |||
Accessing/Clearing range in hidden workbook | Excel Programming |