VB code for conditional formatting on live value cell?

[slimimi]

Hi there,\

Sorry for my long Title description - hope i got it right !!

Is there a way to look at a cell's own value and then format it accordingly using VB?

eg,

I have a cell whose value changes based on DDE link to live price data.
Cell is "Q5".

When the new value is greater than the old value - i want to format the pattern color of the cell.

When the new value is less than the old value - i want to format the pattern color of the cell.

Does anyone know how to do this please or is there a way to do this using spreadsheet formulas as oppose to VB?

THanks in advance for looking in..

 

[slimimi]

Thank you Tom. I assume this method is doing away with the textboxes - correct?

I have changed all the code you mentioned and also changed the formulas in the cells S5, S8, etc...

I have saved this as a new workbook called "Test".
However, when i reopen this "Text" workbook i always get the following message:

Remote Data not accessible.
Do you want to start DDESERVER.EXE?

This is the code i have so far from you with the changes :

Option Explicit

Private LinkRanges(6) As Range
Private PreviousLinkRangeValues(6) As Currency

Private Sub Workbook_Open()
StartWatchingLinks
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
StopWatchingLinks
End Sub

Friend Sub StartWatchingLinks()
Dim x As Integer

On Error Resume Next
For x = 0 To 6
With Me.Worksheets("positions")
Set LinkRanges(x) = .Range(Choose(x + 1, "S5", "S8", "S11", "S14", "S17", "S20", "S23"))
PreviousLinkRangeValues(x) = LinkRanges(x).Value
End With
Next

ThisWorkbook.SetLinkOnData "MT4|BID!AUDUSDm", "'ThisWorkbook.OnLinkUpdate ""0""'"
ThisWorkbook.SetLinkOnData "MT4|BID!EURAUDm", "'ThisWorkbook.OnLinkUpdate ""1""'"
ThisWorkbook.SetLinkOnData "MT4|BID!EURGBPm", "'ThisWorkbook.OnLinkUpdate ""2""'"
ThisWorkbook.SetLinkOnData "MT4|BID!EURUSDm", "'ThisWorkbook.OnLinkUpdate ""3""'"
ThisWorkbook.SetLinkOnData "MT4|BID!GBPUSDm", "'ThisWorkbook.OnLinkUpdate ""4""'"
ThisWorkbook.SetLinkOnData "MT4|BID!USDCHFm", "'ThisWorkbook.OnLinkUpdate ""5""'"
ThisWorkbook.SetLinkOnData "MT4|BID!USDJPYm", "'ThisWorkbook.OnLinkUpdate ""6""'"
End Sub

Friend Sub StopWatchingLinks()
ThisWorkbook.SetLinkOnData "MT4|BID!AUDUSDm", ""
ThisWorkbook.SetLinkOnData "MT4|BID!EURAUDm", ""
ThisWorkbook.SetLinkOnData "MT4|BID!EURGBPm", ""
ThisWorkbook.SetLinkOnData "MT4|BID!EURUSDm", ""
ThisWorkbook.SetLinkOnData "MT4|BID!GBPUSDm", ""
ThisWorkbook.SetLinkOnData "MT4|BID!USDCHFm", ""
ThisWorkbook.SetLinkOnData "MT4|BID!USDJPYm", ""
End Sub

Friend Sub OnLinkUpdate(LinkIndex As Integer)
On Error GoTo ErrOnLinkUpdate
If LinkRanges(LinkIndex).Value > PreviousLinkRangeValues(LinkIndex) Then
LinkRanges(LinkIndex).Interior.Color = vbBlue
ElseIf LinkRanges(LinkIndex).Value < PreviousLinkRangeValues(LinkIndex) Then
LinkRanges(LinkIndex).Interior.Color = vbRed
End If
PreviousLinkRangeValues(LinkIndex) = LinkRanges(LinkIndex).Value
ErrOnLinkUpdate:
End Sub

Is it correct what i have done?

I can't see where, in the code, it is referencing to DDESERVER - so i am not quite sure why its still asking me if i want to start that application.

Thanks in advance for your amazing help and kindness Tom.

Best Regards,
Imran

 

[Legacy 98055]

Are you sure that you removed all of the links that have DDESERVER in them? Do a CTRL-F on your worksheet and search for DDESERVER. The cell links need to be the same as

MT4|BID!AUDUSDm
MT4|BID!EURAUDm
...

 

[slimimi]

Tom can i be a BIG PAIN now and ask you if its possible, for each currency's live price data, to keep everything the same (as in "S5, S8", etc) as the link data BUT to change the target cell for the color changes, ie, Cell A5 change color for Cell S5 link data, Cell A8 change color for Cell S8 link data, etc, etc...

Reason why i ask is because i realized i have conditional formatting on Column S which sort of acts as an alert for me (from glancing at the screen from a distance) to show me if i need to tell the kids to play and spend 3 minutes investigating that trade further (he he).. Hope this makes sense.

So i think if A5 gives me the indication of whether price is moving up or down from previous price - this would be better.

Thanks a million in advance...

 

[slimimi]

Are you sure that you removed all of the links that have DDESERVER in them? Do a CTRL-F on your worksheet and search for DDESERVER. The cell links need to be the same as

MT4|BID!AUDUSDm
MT4|BID!EURAUDm
...

I did what you suggested Tom but Excel did not find any matches.
Hmmm - this is really weird huh !!!

 

[slimimi]

I also noticed that Sheet 1(postions) code had the following in it:

Option Explicit

With nothing else after that. Is that correct?

 

[Legacy 98055]

Friend Sub OnLinkUpdate(LinkIndex As Integer)
    On Error GoTo ErrOnLinkUpdate
    If LinkRanges(LinkIndex).Value > PreviousLinkRangeValues(LinkIndex) Then
         LinkRanges(LinkIndex).Offset(, -18).Interior.Color = vbRed
    ElseIf LinkRanges(LinkIndex).Value < PreviousLinkRangeValues(LinkIndex) Then
        LinkRanges(LinkIndex).Offset(, -18).Interior.Color = vbBlue
    End If
    PreviousLinkRangeValues(LinkIndex) = LinkRanges(LinkIndex).Value
ErrOnLinkUpdate:
End Sub

Did you see my previous reply? Were you able to get rid of the references to DDESERVER?

 

[Legacy 98055]

Correct. All code is in the workbook.

 

[Legacy 98055]

Run this from the VBAIDE and hit CTRL-G5 to bring up the immediates window. Paste the results in your next repy...

Sub RunThis()
    Dim aLinks, i
    aLinks = ActiveWorkbook.LinkSources(xlOLELinks)
    If Not IsEmpty(aLinks) Then
        For i = 1 To UBound(aLinks)
            Debug.Print "Link " & i & ":" & Chr(13) & aLinks(i)
        Next i
    End If
End Sub

BTW. For your offset color in column A

Friend Sub OnLinkUpdate(LinkIndex As Integer)
    On Error GoTo ErrOnLinkUpdate
    If LinkRanges(LinkIndex).Value > PreviousLinkRangeValues(LinkIndex) Then
         LinkRanges(LinkIndex).Offset(, -18).Interior.Color = vbRed
    ElseIf LinkRanges(LinkIndex).Value < PreviousLinkRangeValues(LinkIndex) Then
        LinkRanges(LinkIndex).Offset(, -18).Interior.Color = vbBlue
    End If
    PreviousLinkRangeValues(LinkIndex) = LinkRanges(LinkIndex).Value
ErrOnLinkUpdate:
End Sub

 

[slimimi]

Hi tom - thank you.
I am sorry - i dont know how to run VBAIDE.
Please would you be so kind as to advise me on this.

THanks in advance.

 

[Legacy 98055]

From a worksheet. ALT-F11 to open the VBAIDE. Paste in the macro, press CTRL-G to show the immediates window or click on View-Immediate Window. Run this:

Sub RunThis()
    Dim aLinks, i
    aLinks = ActiveWorkbook.LinkSources(xlOLELinks)
    If Not IsEmpty(aLinks) Then
        For i = 1 To UBound(aLinks)
            Debug.Print "Link " & i & ":" & Chr(13) & aLinks(i)
        Next i
    End If
End Sub

Debug.Print "Link " & i & ":" & Chr(13) & aLinks(i)
this line will output to the immediates window.