Tags: conditional, continuously, direction, excel, formatting, functions, microsoft, msdn, point, real, retrieving, rtd, software, spreadsheet, time

Conditional Formatting / RTD functions

On Microsoft » Microsoft Excel

10,904 words with 5 Comments; publish: Fri, 23 May 2008 06:51:00 GMT; (306105.47, « »)

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.

All Comments

Leave a comment...

  • 5 Comments
    • 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" <nospam_mm.excel.todaysummary.com.yahoo.com> wrote in message

      news:b0f73dc8.0409030845.40605e20.excel.todaysummary.com.posting.google.com...

      > 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.

      #1; Fri, 23 May 2008 06:52:00 GMT
    • 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" <nospam_mm.excel.todaysummary.com.yahoo.com> wrote in message

      news:b0f73dc8.0409030845.40605e20.excel.todaysummary.com.posting.google.com...

      > 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.

      #2; Fri, 23 May 2008 06:53:00 GMT
    • > 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" <raseysm.excel.todaysummary.com.wiserways.com> wrote in message

      news:O2iwVrdkEHA.1800.excel.todaysummary.com.TK2MSFTNGP15.phx.gbl...

      > 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" <nospam_mm.excel.todaysummary.com.yahoo.com> wrote in message

      > news:b0f73dc8.0409030845.40605e20.excel.todaysummary.com.posting.google.com...

      > > 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.

      >

      #3; Fri, 23 May 2008 06:54:00 GMT
    • 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.

      #4; Fri, 23 May 2008 06:55:00 GMT
    • That should work.

      --

      Regards,

      Tom Ogilvy

      "Hercules" <nospam_mm.excel.todaysummary.com.yahoo.com> wrote in message

      news:b0f73dc8.0409040828.6bd936c9.excel.todaysummary.com.posting.google.com...

      > 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.

      #5; Fri, 23 May 2008 06:56:00 GMT