=IF(Color=green) ?

L

LookStrike

How can i decide if a cell has a background color green, do
something...
like
=if(G6:Color=Green;"something";"other")

And,
In the same idea, if i have a column with date...
I want that the date is checked in the second sheet and if is between 2
dates mentionned in the second page, i want to put a the color that the
cell of the second page has.

omg, tough no ? :p

:confused:

Thanks for helping me !
 
B

Bob Phillips

Can't do it directly, you need VBA.

Here is a function that gets the colorindex

Put this VBA function in a code module. Check for green with

=IF(ColorIndex(G6)=10;"something";"other")

Or, set a cell, say A1, to the color green, and then you use something like

=IF(ColorIndex(G6)=Colorindex(A1);"something";"other")

You coukld even setup a named range called Green to refer to =Colorindex(A1)
and use

=IF(Colorindex(G6)=Green;"something";"other")

You can even sum coloured cells using this worksheet function to count the
greens in A1:A100
=SUMPRODUCT(--(Colorindex(A1:A100)=10))


Function ColorIndex(rng As Range) As Variant
Dim r As Range, c As Range, i As Long, j As Long
Dim rv As Variant

If rng.Areas.Count > 1 Then
ColorIndexAlt = CVErr(xlErrValue)
Exit Function
End If

If rng.Cells.Count = 1 Then
rv = rng.Interior.ColorIndex

Else
rv = rng.Value
i = 0

For Each r In rng.Rows
i = i + 1
j = 0

For Each c In r.Cells
j = j + 1

rv(i, j) = c.Interior.ColorIndex
Next c

Next r

End If



--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
D

David Turner

Chip Pearson wrote
You have to use VBA procedures to read the ColorIndex property of the
cell. See the CellColorIndex function listed at
www.cpearson.com/excel/colors.htm
Click to expand...

I had a similar request in another thread, only for cells based on
conditional formatting. After perusing your link for CF functions, I
decided I didn't want to dazzle others that much <sigh>.
 
Top