I haven't used confdtional formating yet in 2007. Don't have it on my pc at
work. but in 2003 you can select Formula Is ( not cell is) and create a
formula to test the two values in the same row. then copy the formula down
the column using PasteSpecial and selecting Value.

"Christian Schratter" wrote:

Hey Joel,

thanks for your input. Your explanation concerning function- and
sub-possibilties might be an explanation to the issue (although I wish it
would be different).

Actually I tried to manually implement conditional formating, given such a
table:

Product | old price | new price
apple 1 5
banana 10 7
computer 1000 800

I wanted to color the "new price" column depending if the old price was
higher or lower than the new price. At first I tried to do this with
conditional formating, but to my knowledge you can not set the rules for the
excel 2007 innate conditional formatting that way?!

Kind regards
Christian

"Joel" wrote:

First, a change in the color of a cell does not cause a re-calculate to occur
on the worksheet. If you changed the value in the cell then the function ill
be executed.

Second, You should not reference a worksheet cell from inside a function.
If you want to use the value in A1 then pass A1 as a parameter

Public Function testColor(Target as range)

MsgBox Target.Interior.Color 'does work - displays e.g. 255
Target.Interior.Color = 10 'does NOT work - displays "#VALUE!"
'in the calling cell
End Function

call function from spreadsheet with
=testcolor(A1)


Third, the code above will not work. A function can only return a value
(not a color change) to the cell where the function is located and not a
different cell. Sub can change any cell but must be manually executed or use
an event.

You best choice might be to use a worksheet change event. Not sure what you
are really trying to do.