Conditional Formatting / RTD functions

[Hercules]

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.

 

[Tom Ogilvy]

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.

 

[Stephen Rasey]

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

 

[Tom Ogilvy]

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.

 

[Hercules]

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.

 

[Tom Ogilvy]

That should work.