Can a formula determine if a cell is a particular colour?

  • Thread starter Thread starter Tino
  • Start date Start date
T

Tino

Hi,

I have an Excel 97 spreadsheet with heaps of rows in it, some of which
have red rather than black text.

Is it possible to have a formula that can show me the rows that are
red so I can then sort on that column.

For example, a formula something like:

=if(cellcolour(A3)=red,1,0)

Thanks a lot for any help.
 
Tino,

No worksheet formula, but you could create a simple UDF. You could try this
technique that evolved out of previous threads between
Harlan Grove and I. Create a function to get the colorindex of cells, namely

Function ColorIndex(rng As Range, Optional font As Boolean = False) As
Variant
Dim cell As Range, row As Range
Dim i As Long, j As Long
Dim aryColours As Variant

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

If rng.Cells.Count = 1 Then
If font Then
aryColours = rng.font.ColorIndex
Else
aryColours = rng.Interior.ColorIndex
End If

Else
aryColours = rng.Value
i = 0

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

For Each cell In row.Cells
j = j + 1

If font Then
aryColours = rng.font.ColorIndex
Else
aryColours(i, j) = cell.Interior.ColorIndex
End If

Next cell

Next row

End If

ColorIndex = aryColours

End Function

and test the cell using

=IF(ColorIndex(A3,TRUE)=3,1,0)


--

HTH

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

Is a UDF just another name for a macro? A function? What does it
stand for?

Thanks
 
Put this in a regular module

Function whatcolor(x)
whatcolor = x.Interior.ColorIndex
End Function

then where red is 3
=IF(whatcolor(B3)=3,1,0)
 
Back
Top