I think you'll be fighting Excel every step of the way to achieve this, but I've
never investigated changing cell format as a side-effect - only changing the
CONTENT of other cells, which I have encountered on a few occasions and not
bothered to pursue.

One option that leaps to mind would be to use an asynchronous subroutine to look
at your public data and reformat the cell after the function  had done its thing.
More precisely, you'd get the function to trigger the execution of the
asynchronous subroutine using a timed delay set to "now".

Call Application.OnTime(Now, "SideEffectSubroutine")

I don't think I've ever tried to run an OnTime from a cell formula function, but
I'd anticipate that it wouldn't be blocked.

Take a look at conditional formatting. For example

http://j-walk.com/ss/excel/usertips/tip006.htm

Excel "to 007" has some really interesting extensions.

http://blogs.msdn.com/excel/

If you reread the original question, I believe you will find that the interest
is on formatting parts of a cell (individual characters) - not the whole cell.
Also that the part about changing the background colour wanted eight different
colours. Both of these are well beyond conditional formatting.

I was aware of what he was asking, I was just making a suggestion of an
alternate approach that in the next version of Excel, that will be out later
this year, offers far more possibilities. Rather than one conditional format
per cell you can have many.

As to the multi colouring, I can only see that being done through VBA. The
actual logic on what character changes to what colour is not that hard. The
actual colour change  can be done with

ActiveCell.Characters(Start:=1, Length:=1).Font.ColorIndex = 12
ActiveCell.Characters(Start:=2, Length:=1).Font.ColorIndex = 17
ActiveCell.Characters(Start:=3, Length:=1).Font.ColorIndex = 4
ActiveCell.Characters(Start:=4, Length:=1).Font.ColorIndex = 46

>Rather than one conditional format
per cell

er.... being a really annoying nitpicker I gues but....Three... I think

No need to duck, I'm the mild one, just don't get on Dian's bad side. ;-)

One of the demos of Excel 2 007 I saw had twelve conditions of the sort
(if value = 1 then fill colour = red...)
So it looks like they can handle more than three conditions.

You didn't point out that you were talking about future capabilities - yes,
additional conditional formatting capability will be nice, but not of much use
now.

I think you'll find that the original questioner is aware that the
multi-colouring can only be done with VBA. I certainly am. That was why he
asked about the ability to get a [vba] function  called from a cell formula to
affect other cells. He's already half-way there, and I was supplying one way of
moving the next step.

You didn't point out that you were talking about future capabilities - yes,
additional conditional formatting capability will be nice, but not of much use
now.

I think you'll find that the original questioner is aware that the
multi-colouring can only be done with VBA. I certainly am. That was why he
asked about the ability to get a [vba] function  called from a cell formula to
affect other cells. He's already half-way there, and I was supplying one way of
moving the next step.

As a side barb... While I can offer no help with the "individual characters"
portion, the 3 condition limit with conditional formatting can possibly be
worked around via a lookup table. I've used the following technique to allow
users to change  series names & colors on a group of locked charts.

An protected lookup table contains three columns: "Index", "Series Name" &
"Color". The cells  for "Series Name" & "Color" are unlocked and the user is
instructed to change the "Series Name" to the desired text for each series name
and to manually change the cell fill color for the "Color" cell to the desired
color. When the code is executed, a Vlookup of "Index" reads/changes desired
"Series Name", and also sets the series color to match the Interior.ColorIndex
property of "Color".

If the "conditions" can be defined and assigned an "Index", the above technique
might provide an easily customizable method to conditionally format according to
an unlimited number of conditions.

Worksheet change  is a good candidate, and aesthetically more "nice"
than the idea of an asynchronous routine fire-off.

However (i.e. no free lunch), the worksheet change event is a bit scatter-gun,
in that it fires with a change in any cell, whether you're interested in that
one or not. So you've then got to work out whether you should do the work on
the cell. I certainly use this approach in some instances, and ignoring
unwanted cells  isn't particularly cumbersome.

I wouldn't even call it robust. In these groups and forums, there is usually
more people involved than a person asking and a person answering a question.
There are usually a number of people with similar questions or partial
suggestions. So most questions can be considered the starting point of a
discussion. The question posed may not be answerable by an individual, but
the group may come up with enough bits and pieces for a workable solution.
So it may appear that you have started a fight, it's just a number of people
throwing out information that may help.

Depending on your time frame, the conditional formatting should be a
solution when the next version of Office is released later this year.

I've had some "issues" with conditional formatting before when I've tried to
copy and paste using VBA. Have you any idea if that is still the case
please?

Do you have an example?
I may be able to ask someone to try it out on Excel 2007.

can't find my example... I think because I fixed it :-) but I
have found a note in my code pointing me at
http://support.microsoft.com/kb/231144/en-us

I don't have the time right now to play around with creating a function,
but I do have a macro which will color and concatenate two cells
together.

For your pleasure:

Sub ColorAndConcatenate()
'Concatenates two cells  together and returns them
'Formatted. First portion will return in Arial Black 12pts
'While second portion will return in Times New Roman 36pts, Red
Dim intCharFirst As Integer
Dim intCharSec As Integer
Dim strFirst As String
Dim strSecond As String

Do While ActiveCell.Value <> ""
strFirst = ActiveCell.Value
intCharFirst = Len(strFirst)

'Move to next cell in list
ActiveCell.Offset(1, 0).Range("A1").Select
strSecond = ActiveCell.Value
intCharSec = Len(strSecond)

'Move to next cell in list
ActiveCell.Offset(1, 0).Range("A1").Select

'Concatenate cells together
ActiveCell.Value = strFirst & strSecond

'Format the New Cell
'First Part
ActiveCell.FormulaR1C1 = strFirst & strSecond
With ActiveCell.Characters(Start:=1, _
Length:=intCharFirst).Font
.Name = "Arial Black"
.FontStyle = "Regular"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With

'Second Part
With ActiveCell.Characters(Start:=intCharFirst + 1, _
Length:=intCharSec).Font
.Name = "Times New Roman"
.FontStyle = "Regular"
.Size = 36
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 3
End With
'Reset Variables
strFirst = ""
strSecond = ""
intCharFirst = 0
intCharSec = 0

'Move to next cell in list
ActiveCell.Offset(1, 0).Range("A1").Select

Loop

End Sub

Could you give a little more info on the sort of troubles you've had? I often
set new values into cells  from vba  without compromising their formatting
(including conditional formatting).

If you copy/paste over a cell (from the keyboard as well as from VBA), the
target cell will of course lose its original formatting. But if you use
paste/special and avoid pasting the formatting, you'll allow the target cell's
format to remain intact.