Applying a function to part of an array w/o loosing data

Hi Blake,

Regarding the function question, the answer is "not exactly, but almost." Since the average function you use in your example is not a built-in VBA function, I assume that it is a user-defined function (UDF) that you have written. This being the case, why not just give it two optional arguments, the upper and lower bound of the input array, so that it can take this into account when desired. Assuming it computes the average of the inputs it would be:

Function average(InArray as Variant, Optional Lower As Integer, Upper As Integer) As Double

Dim L As Integer
Dim U As Integer
Dim i As Integer

If IsMissing(Lower) Then
L = LBound(InArray)
Else
L = Lower
End If
If IsMissing(Upper) Then
U = UBound(InArray)
Else
U = Upper
End If

average = 0
For i = L To U
average = average + InArray(i)
Next i

average = average / (U - L + 1)

End Function

Then you could call this function as:

avg = average(arraysample,5,10)

but if you wanted to average all the elements in the array:

avg = average(arraysample)

Regarding the second question, yes, you could do this, but what is the macro doing between the time you start it running and the time you click on a cell? Why not select the cell or cells first, and then run a macro that changes the color of all the selected cells? This would not then require a key stroke to end the macro, and it also would not have to run continuously while you select cells. Such a routine would look like:

Sub ColorCells
Selection.ColorIndex = 5
End Sub

Happy computing.

Damon