Hopefully someone can point me in the right direction here. I have a
spreadsheet with RTD functions that are continuously retrieving real
time data.

What I need to do is look at the values in the RTD columns and display
certain special characters in other columns. Specifically, in any
given cell, I would need to display a string of text consisting of 3
different fonts and 3 different colors.

Conditional formatting only seems to work at the cell level and not
the character level so I can't use it. That leaves me with using the
Worksheet_Change event. I've written the VBA code to create the
special character string but the problem is that when the RTD values
change, the Worksheet_Change event never fires.

Does anyone know how to detect when the value returned by an RTD
function changes? Alternatively, does anyone know how I can use
conditional formatting to accomplish this?

Thanks for your help.

Here is an untested idea:

in a cell put in a function like

=Count(A2,B9,C4,F6)

where the cells are the ones containing your RTD functions. This should
generate a calculate when the cells are updated (untested, I don't have
xl2002 or later installed).

then use the calculate event to update your alert cells.

Conditional formatting will not do the rich text formatting you describe.
Additionally, from what I read, RTD doesn't generate a calculate event on
its own change and conditional formatting is updated on a calculation.

--
Regards,
Tom Ogilvy

"Hercules" wrote in message
om...
Hopefully someone can point me in the right direction here. I have a
spreadsheet with RTD functions that are continuously retrieving real
time data.

What I need to do is look at the values in the RTD columns and display
certain special characters in other columns. Specifically, in any
given cell, I would need to display a string of text consisting of 3
different fonts and 3 different colors.

Conditional formatting only seems to work at the cell level and not
the character level so I can't use it. That leaves me with using the
Worksheet_Change event. I've written the VBA code to create the
special character string but the problem is that when the RTD values
change, the Worksheet_Change event never fires.

Does anyone know how to detect when the value returned by an RTD
function changes? Alternatively, does anyone know how I can use
conditional formatting to accomplish this?

Thanks for your help.

I can think of no way to do what you want without a subroutine.

Conditional formatting cannot change the Font. It can only color the
entire cell, not a part of it.

You can color and change the font of a fixed Text string, but you loose that
ability in any formula.
For instance you can enter into a cell "RedBlue" . Select the Red, Font
color Red. Select the "Blue", Font color Blue. You will get a two-tone
entry into the cell.
Chance the entry to ="RedBlue" and you cannot select the Blue and color it
different from the Red.

If the requirement is essential, then you will have to create a subroutine
that will enter into the cell a value as a fixed text string. Then use the
Range.Characters collection.

With rngCell.Characters(Start:=intStart, Length:=intLen).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 55
Endwith

Good luck. I hope you get a more helpful answer.

Stephen Rasey
Houston
http://wiserways.com
http://excelsig.org


"Hercules" wrote in message
om...
Hopefully someone can point me in the right direction here. I have a
spreadsheet with RTD functions that are continuously retrieving real
time data.

What I need to do is look at the values in the RTD columns and display
certain special characters in other columns. Specifically, in any
given cell, I would need to display a string of text consisting of 3
different fonts and 3 different colors.

Conditional formatting only seems to work at the cell level and not
the character level so I can't use it. That leaves me with using the
Worksheet_Change event. I've written the VBA code to create the
special character string but the problem is that when the RTD values
change, the Worksheet_Change event never fires.

Does anyone know how to detect when the value returned by an RTD
function changes? Alternatively, does anyone know how I can use
conditional formatting to accomplish this?

Thanks for your help.

I've written the VBA code to create the
special character string but the problem is that when the RTD values
change, the Worksheet_Change event never fires.

--
Regards,
Tom Ogilvy


"Stephen Rasey" wrote in message
...
I can think of no way to do what you want without a subroutine.

Conditional formatting cannot change the Font. It can only color the
entire cell, not a part of it.

You can color and change the font of a fixed Text string, but you loose
that
ability in any formula.
For instance you can enter into a cell "RedBlue" . Select the Red, Font
color Red. Select the "Blue", Font color Blue. You will get a two-tone
entry into the cell.
Chance the entry to ="RedBlue" and you cannot select the Blue and color it
different from the Red.

If the requirement is essential, then you will have to create a subroutine
that will enter into the cell a value as a fixed text string. Then use
the
Range.Characters collection.

With rngCell.Characters(Start:=intStart, Length:=intLen).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 55
Endwith

Good luck. I hope you get a more helpful answer.

Stephen Rasey
Houston
http://wiserways.com
http://excelsig.org


"Hercules" wrote in message
om...
Hopefully someone can point me in the right direction here. I have a
spreadsheet with RTD functions that are continuously retrieving real
time data.

What I need to do is look at the values in the RTD columns and display
certain special characters in other columns. Specifically, in any
given cell, I would need to display a string of text consisting of 3
different fonts and 3 different colors.

Conditional formatting only seems to work at the cell level and not
the character level so I can't use it. That leaves me with using the
Worksheet_Change event. I've written the VBA code to create the
special character string but the problem is that when the RTD values
change, the Worksheet_Change event never fires.

Does anyone know how to detect when the value returned by an RTD
function changes? Alternatively, does anyone know how I can use
conditional formatting to accomplish this?

Thanks for your help.

Thanks for the responses. I was afraid you were going to say that ...
worksheet_calculate is my only option for being alerted to the changes
and conditional formatting as is won't solve my problem. I don't
think I'll use the calculate event since I have about 5 columns that
need to display alerts and the spreadsheet may have up to 500 entries.
For a real time application, it maybe too big of a performance hit to
loop through and call my formatting function for 2500 entries on every
calculate.

This morning I actually had another idea that might accomplish what I
need. Since my alert cells have 3 fonts and 3 different colors, I'm
thinking of breaking them down into 3 different adjacent columns such
that each column only uses one font and color. White cell borders
should provide the appearance of one cell when in fact it is three. I
haven't tried this yet but I don't even think I'll need to use
conditional formatting. I should be able to just initially set the
colors/fonts of each column and then use formulas to display the
appropriate number of characters.

That should work.

--
Regards,
Tom Ogilvy

"Hercules" wrote in message
om...
Thanks for the responses. I was afraid you were going to say that ...
worksheet_calculate is my only option for being alerted to the changes
and conditional formatting as is won't solve my problem. I don't
think I'll use the calculate event since I have about 5 columns that
need to display alerts and the spreadsheet may have up to 500 entries.
For a real time application, it maybe too big of a performance hit to
loop through and call my formatting function for 2500 entries on every
calculate.

This morning I actually had another idea that might accomplish what I
need. Since my alert cells have 3 fonts and 3 different colors, I'm
thinking of breaking them down into 3 different adjacent columns such
that each column only uses one font and color. White cell borders
should provide the appearance of one cell when in fact it is three. I
haven't tried this yet but I don't even think I'll need to use
conditional formatting. I should be able to just initially set the
colors/fonts of each column and then use formulas to display the
appropriate number of characters.