Interrogating format

[Jack Schitt]

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.

 

[keepitcool]

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]

Thanks for that.

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 >