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]

ok Tom thank you - i will try this and let you know the results.
Last question please (sorry to keep troubling you).

I am wondering if its possibly to modify the code to place a formula in Cell S5, S8, etc, instead, based on what the user enters for currency into Cell A5.

I have previously done this using the Substitute and indirect functions but i am not sure how we can get round this one to change the vb code for the 'set link on data' to automatically represent what the user enters into A5 or A8, or A11, etc, as a currency.

Example - user enters AUD/USD into Cell A5.
So now all the 'Set Link on Data' code needs to change (for that line) to =MT4|BID!AUDUSDm

Do you think this is possible?

Reason i am asking is because i will be adding on currencies eventually as i go along and also offering this sheet to other users who might want to trade completely different currencies than me.

Hope this makes sense.
Thanks in advance.

Sorry again for troubling you with all this.
YOu have been a magnificent help.. Thanks ))

 

[Legacy 98055]

Sure. But before we even bother changing something that does not work to begin with... Is what we already have working or not?

 

[slimimi]

Thank you Tom.
Ok - i try typing in a value in S5, etc, but it does not change the color in the offset cell. I guess this is because its looking for a cell with formula =MT4, etc... BUT, when i overwrite that formula by typing in a number in the cell then it doesn't work. I am assuming here (with my limited knowledge).

Therefore, i thought i would have to wait until markets open on Monday before i see any live price feed changes - which is when i will be able to tell you for definite how we are getting on. Would that be ok? Or is there another way i can test it?

Your workbook example works perfectly btw - so i guess - its just waiting to see how it works now that its all applied to my sheet.

THanks in advance.

 

[Legacy 98055]

Yes. I would use a data validation list so you can control what gets entered into these ranges. A5,8,11,...

Enter a list of the possible values that have corresponding DDE LinkItem names.

Example. The linkitem used for AUD/USD is AUDUSDm. Create a single column list on a hidden or otherwise unused worksheet. List all of your combinations.

AUD/USD
EUR/AUD
ect...

Select this list and name it CurrencyList. Add validation using this list to your cells in column a on sheet positions. If you need help with that, please search. There is a lot of info for validating ranges using a list. Select the validated cells in column a and name this range CurrencySelections.

Back to your list. One column over, enter in the LinkItem names

You will end up with something like this. Ignore the headers.

User Selection            LinkItemName
AUD/USD                   AUDUSDm
...                            ...

After doing the above, it is elementary using the sheet change event. Post back if you need help with that.

 

[slimimi]

Hi tom - this sounds great - will try this out.
Unfortunately - i do need help with the elementary using sheet change event please.
Much appreciated if you can.

Will let you know soon regarding the color change cells.
All the best

 

[Legacy 98055]

Mimi. How is it running? Were you able to apply the directions above? I don't want to waste time writing code with specifications that don't exist...

 

[slimimi]

Hi tom - sorry for the delay will get back to you very soon - i came across some issues when i was trying to put code into my sheet - but almost figured it all out (user mistakes - nothing wrong with the code - just me thats all).... Will post back soon.

 

[Legacy 98055]

10-4

 

[slimimi]

Hi Tom - ok - i have to give in now. After several hours of trying this and that - i just cant get it to do anything. I don't know if perhaps its conflicting with some other code or something. This is the full code i have in the sheets module.

'Toms Code

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).Offset(, -3).Interior.Color = vbBlue
ElseIf LinkRanges(LinkIndex).Value < PreviousLinkRangeValues(LinkIndex) Then
LinkRanges(LinkIndex).Offset(, -3).Interior.Color = vbRed
End If
PreviousLinkRangeValues(LinkIndex) = LinkRanges(LinkIndex).Value
ErrOnLinkUpdate:
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)


''Code Changes Number Format between 2 decimal places and 4 decimal places
''depending on if "JPY" is detected in that row


Dim myRange As Range
Dim c As Range
Dim myFormat As String

Set myRange = Intersect(Target, Columns("A"))
If Not myRange Is Nothing Then
Application.ScreenUpdating = False
For Each c In myRange
If InStr(UCase(c.Value), "JPY") Then
myFormat = "0.00"
Else
myFormat = "0.0000"
End If
Intersect(Rows(c.Row), Range("F:F,J:J,H:H,L:L,N:N,P:P,T:T,S:S,V:V")).NumberFormat = myFormat
Next c
Set myRange = Nothing
Application.ScreenUpdating = True
End If

End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

''This code is supposed to Call Beep based on cell criteria
''In this case - IF cell value falls below 100 and then above 100 then BEEP


Static aboveLimit As Boolean
If 100 < Application.Min(Range("G5:O5")) Then
If Not (aboveLimit) Then Call sndPlaySound32("C:\documents and settings\" & Environ("USERNAME") & "\desktop\Positions\Sounds\Beep.WAV", 0)

aboveLimit = True
Else
aboveLimit = False
End If


End Sub

Sorry about this.
Hope you can advise me further.

 

[slimimi]

Tom - regarding the other stuff.
I have done as you suggested.

Created new Sheet
In new sheet Created List in Column A called User Selection
In new sheet Created List in Column B called Link Item Name
In new sheet - Highlighted A2:A20 - named range "CurrencyList"

In original sheet - Created data validation in A5, A8, A11, A14, A17, A20 & A23
In original sheet - Highlighted the above cells - named range "CurrencySelections"

I am not sure how to do the elementary part now

Regarding the initial code - i still cant get it to do anything on my particular sheet.
The DDEserver example works fine with your DDEServer.exe

but when i replace code names of link0, for example, to MT4|BID!AUDUSDm and then go to cell S5 and type in =MT4|BID!AUDUSDm, my price feed continues to grab live data in cell S5 but i dont see any color changes anywhere on the sheet.

Hmmmm

`