In-cell formula/function to determine the color of a cell?

S

sherifffruitfly

Hi all,

I've been given a sheet with some rows black, and others red. I'd like
to put a 1 or a 0 in a column depending on which color the adjacent
cell is. Is there an in-cell formula that will do this?


TIA,

cdj
 
S

Shane Devenshire

Hi,

If you mean is there a built-in function to do this - the answer is no. You
will need to build a VBA funtions for this.

Here is a simple VBA function

Function myColor(R As Range) As Integer
If R.Interior.ColorIndex = 1 Then
myColor = 1
ElseIf R.Interior.ColorIndex = 3 Then
myColor = 0
End If
End Function

If this helps, please click the Yes button.

Cheers,
Shane Devenshire
 
M

Mais qui est Paul ?

Bonsour® sherifffruitfly avec ferveur ;o))) vous nous disiez :
I've been given a sheet with some rows black, and others red. I'd like
to put a 1 or a 0 in a column depending on which color the adjacent
cell is. Is there an in-cell formula that will do this?

Select B1
Insert > Name > Define
enter : LColor
refers to :
=GET.CELL(38,A1)
OK

Use :
Suppose Cell G5 is red (Format , Cell choose Color=Red)
in F5 type : = LColor
in F5 you'll see : 3

(it's the ColorIndex of left cell you type LColor)

Beware Changing color is not an event causing Recalculation !!!
always use F9
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top