Home |
Search |
Today's Posts |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set cell color with VBA results in "#VALUE!"
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. |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
Different Font Color for results evaluated by "IF" | New Users to Excel | |||
set "value if true" to "fill cell with color" | Excel Programming | |||
Using the results of CELL function ("address" info type) | Excel Programming | |||
How can I make cell A1 a "Y" or "N" depending upon cell A2's font color? Please help. | Excel Programming |