Interrogating format
 

How would I go about interrogating the format and/or custom format of a
cell, please? I am assuming that this has to be done by VBA hence I have
only posted the question in this newsgroup, but if there is a non-VBA
solution I would be interested.

Examples might be:

1) A function: =FUNC(cell_ref) should return the text string "green" if the
background colour of cell_ref is green

2) I might want to sort a range of cells by the background colour of cells
in Column B.

There are numerous other possibilities, so I am only looking for general
pointers (or a URL that may explain it in depth).

Thanks.

--
Return email address is not as DEEP as it appears

Interrogating format
 

Try:

Function CellColor(Optional Reference As Range) As Long

If Reference Is Nothing Then Set Reference = Application.Caller
With Reference.Cells(1).Interior
If .ColorIndex = xlColorIndexNone Then
CellColor = 0
Else
CellColor = .ColorIndex
End If
End With

End Function


I wouldn't specify volatile.. Changing a cells color does NOT trigger
a change event. So before sorting you'll have to force a recalc.



keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Jack Schitt" wrote:

How would I go about interrogating the format and/or custom format of
a cell, please? I am assuming that this has to be done by VBA hence I
have only posted the question in this newsgroup, but if there is a
non-VBA solution I would be interested.

Examples might be:

1) A function: =FUNC(cell_ref) should return the text string "green"
if the background colour of cell_ref is green

2) I might want to sort a range of cells by the background colour of
cells in Column B.

There are numerous other possibilities, so I am only looking for
general pointers (or a URL that may explain it in depth).

Thanks.

Interrogating format
 

Thanks for that.

"keepitcool" wrote in message
...


Try:

Function CellColor(Optional Reference As Range) As Long

If Reference Is Nothing Then Set Reference = Application.Caller
With Reference.Cells(1).Interior
If .ColorIndex = xlColorIndexNone Then
CellColor = 0
Else
CellColor = .ColorIndex
End If
End With

End Function


I wouldn't specify volatile.. Changing a cells color does NOT trigger
a change event. So before sorting you'll have to force a recalc.



keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Jack Schitt" wrote:

How would I go about interrogating the format and/or custom format of
a cell, please? I am assuming that this has to be done by VBA hence I
have only posted the question in this newsgroup, but if there is a
non-VBA solution I would be interested.

Examples might be:

1) A function: =FUNC(cell_ref) should return the text string "green"
if the background colour of cell_ref is green

2) I might want to sort a range of cells by the background colour of
cells in Column B.

There are numerous other possibilities, so I am only looking for
general pointers (or a URL that may explain it in depth).

Thanks.