Problem - Filter based on color?
 

Hey,

In fact i'm looking for an "inverse" conditional format?

I have a huge price list, and changes in price are marked in red.
What I want is a second column next to the price column that has a
formula that put's a "1" if the price is changed (marked red) and "0"
if the price is unchanged...

Any ideas? Thx in advance.

Marc.


---
Message posted from http://www.ExcelForum.com/

Problem - Filter based on color?
 

Hi Marc

first you have to use a UDF which returns the colorindex of a cell (for
more information see http://www.cpearson.com/excel/colors.htm from
which the following code is pasted):
Function CellColorIndex(InRange As Range, Optional _
OfText As Boolean = False) As Integer
'
' This function returns the ColorIndex value of a the Interior
' (background) of a cell, or, if OfText is true, of the Font in the
cell.
'
Application.Volatile True
If OfText = True Then
CellColorIndex = InRange(1,1).Font.ColorIndex
Else
CellColorIndex = InRange(1,1).Interior.ColorIndex
End If
End Function

Now put the following formula in the secon column:
=IF(CellColorIndex(A1)=[code for your color],1,0)

HTH
Frank

Hey,

In fact i'm looking for an "inverse" conditional format?

I have a huge price list, and changes in price are marked in red.
What I want is a second column next to the price column that has a
formula that put's a "1" if the price is changed (marked red) and "0"
if the price is unchanged...

Any ideas? Thx in advance.

Marc.

Problem - Filter based on color?
 

Thanks Frank, this was all the info I needed :)

Marc.


---
Message posted from http://www.ExcelForum.com/