Color a cell based on another cell... with a function

[scottcolbury]

Hi all,

I'm trying to write a worksheet function that will look at a cell on another worksheet, and then bring back both the value and color of the first cell.

For instance... I'm hoping to write something like this.
=GetColor(Range)

If I entered =GetColor(Sheet1!A1) in cells B2 on sheet2 then I would get both the value and color of Sheet1!A1 in Sheet2!B2.

I did some searching on the net and found ways to sum values based on colors, but nothing quite like this.

Here's the start of my code:

Function GetColor(InputCell As Range) As Double
Dim ColorRange As Integer

Application.Volatile True

    InputVal = InputCell.Value
    ColorRange = InputCell.Interior.ColorIndex

    GetColor = InputVal
    GetColor.Interior.ColorIndex = ColorRange
    
End Function

Obviously the following line doesn't work

GetColor.Interior.ColorIndex = ColorRange

Thanx in advance.
s_c

 

[Norie]

Scott

I'm afraid you can't use a User Defined Function in a worksheet to alter the format of a cell.