Ranijt Konkar said:
In one of my uses, I am using the colour of cells rather than their contents
to signify something. I want colours of some cells to change when I change
colours of some other. Can I set up a formula to handle colour of the cell
rather than the content of it?
An alternative to using VB script would be to duplicate the formula results
of cell 1 into cell 2 (where cell 2 formula is: =cell 1) and then, through
conditional formatting, set up cell 2 coloring by saying "If cell value is
EQUAL TO" the exact same contents of cell 1 (which can be either alpha or
numeric), then format such-and-such a color--ie the same color selection as
you have in cell 1. I believe this approach should work just fine as long as
you are indifferent to the actual written contents of the cells in question.
Also, the cell 2 formula can be an IF statement based on the contents of cell
1 if you only want certain colors to appear under certain conditions which
you set up in cell 1. For example, if you only want green to appear when the
condition in cell 1 is TRUE, then test for the TRUE outcome in cell 2 and set
the conditional formatting to give you green only if the cell 1's contents is
the TRUE outcome: cell 1--If(C2="Now","Later"); cell2--If(C2="Now","Now","")
setting your conditional formatting for cell 2 to say: Condition 1 is Cell
value is EQUAL TO ="Now", then set your format color to green (or whatever)
and click OK.
If you want to extend this latter solution, you could even set up the cell 2
formula to read--If(C2="Now","",".") and set Condition 1 to--Cell value is
EQUAL TO ="" with the color formatting keying off a blank cell. Note that
the FALSE condition in the cell 2 formula is now looking for a decimal point
(or period)--or any other unobtrusive character you care to use to
distinguish the color condition from the non-color condition. You can get as
imaginative as you like with this kind of approach. Just note that the
character you select for the FALSE condition will appear in cell 2 when the
condition in cell 1 is FALSE--thus, the most unobtrusive character you can
live with is what I have suggested. You can, of course, set up a second
conditional formatting statement to have nothing appear, if that is important
to you. Thus, Condition 2--Cell value is EQUAL TO ="." where you set the
Font color to White wil do just the trick. You can get really cute with this
stuff if you are so inclined.
Maybe this is overkill, but I thought you might like to the see the
possibilities, anyway. Hope it helps.
Regards,
Peter
