Excel function returning color of another cell

[MrCorner]

I'm trying to create a function in Excel which returns the color of another cell. The idea is, that the function can be called as '=Background(A1)' - and then it should return the interior color of cell A1. This is what I came up with:

Public Function Background(Data As Range) As Double
Dim nBackgroundcolor As Double

nBackgroundcolor = 0
nBackgroundcolor = Worksheets("Sheet1&quot.Data.Interior.ColorIndex

Background = nBackgroundcolor
End Function

The problem is that, when I debug, 'Data' does not contain 'A1', but the contents of A1. How do I do this?

Thanks in advance!

 

[sfvb]

Here's an example:

Sub TestBackgroundFunction()
Dim myCell As Range

Set myCell = Worksheets("Sheet1&quot.Range("A1&quot
MsgBox Background(myCell)
End Sub

Public Function Background(Data As Range) As Double
Dim nBackgroundcolor As Double

nBackgroundcolor = 0
nBackgroundcolor = Data.Interior.ColorIndex

Background = nBackgroundcolor
End Function

 

[MrCorner]

Thanks, but this is not what I mean. The problem is that I want my function to get the cell 'A1' as an input parameter, while now it gets the value of 'A1'.

 

[TonyJollans]

Hi,

Data is a Range (as defined). It does not need qualifying with a Sheet name. You should just say ..

nBackgroundColor = Data.Interior.ColorIndex

If I understand correctly, when used where it cannot be a range, VBA gives you what it thinks you must want - the default property of the range, its value.

Enjoy,

Tony

 

[MrCorner]

I see now - Thanks for pointing that out

 

[euskadi]

MrCorner,

You need to tell Excel that you want the range, not the value. Try this:

Function background(ByRef data As Range)
Application.Volatile
background = data.Cells.Interior.ColorIndex
End Function

Note that this will update anytime the workbook is calculated, but it won't give you the new value if the only change made to the worksheet is the color. It also doesn't capture any color resulting from conditional formatting, only the underlying background of the cell.