how to automatically record markets in a quick list

[Captain Sensible]

It's a good habit to use

Option Explicit

at the top of your code as it will highlight any undeclared variables. Would be a lot easier if you used modules and Public variables too, best to use the early on whilst you're learning.

[cjones198]

Also why cant i edit anything in a worksheet change on the page labelled selection?

so i have the price and volume linked to two cells in page called selection but when i set up a worksheet event change sub, nothing happens?

[cjones198]

Have you declared switchMarket as a public variable anywhere? It doesn't appear anywhere in the code you've posted so that means it will always be set to false each time the worksheet change runs.

I have not no. so i shall start using option explicit! save me lots of headaches

[Captain Sensible]

What do you mean you can't edit? You can't physically type into that page?

[cjones198]

sorry captain, that variable switch market i had not declared it as "static" silly error

so page one "Market" i designate 2 cells in sheet "selection"
E4 is current price
E5 is current matched

on my manual sheet the only piece of code i had on "market" was

Private Sub Worksheet_Change(ByVal Target As Range)
Dim x As Integer


If Sheets("Market").Cells(2, 5).Value = "In Play" Then

Exit Sub
End If


x = Sheets("Selection").Cells(2, 5).Value + 4

Sheets("Selection").Cells(4, 4).Value = Sheets("Market").Cells(x, 15).Value
Sheets("Selection").Cells(5, 4).Value = Sheets("Market").Cells(x, 16).Value


End Sub

then the bulk of code was in sheet selection which is

Private Sub Worksheet_Change(ByVal Target As Range)



If Price = Empty Then 'If no start price recorded dont do anything
Exit Sub
End If

If EventID <> Sheets("market").Cells(1, 1).Value Then 'If the event changes stop reocording
Price = Empty
Exit Sub
End If

If Sheets("Selection").Cells(8, 4).Value = "STOP" Then
Exit Sub
End If

Application.EnableEvents = False



If Sheets("Selection").Cells(6, 4).Value >= "0.5" Then 'if 2 or more ticks selected

If Sheets("Selection").Cells(4, 4).Value >= 1.01 And Sheets("Selection").Cells(4, 4).Value <= 2 Then
Sheets("Selection").Cells(1, 10).Value = "0.01"
ElseIf Sheets("Selection").Cells(4, 4).Value >= 2.02 And Sheets("Selection").Cells(4, 4).Value <= 3 Then
Sheets("Selection").Cells(1, 10).Value = "0.02"
ElseIf Sheets("Selection").Cells(4, 4).Value >= 3.05 And Sheets("Selection").Cells(4, 4).Value <= 4 Then
Sheets("Selection").Cells(1, 10).Value = "0.05"
ElseIf Sheets("Selection").Cells(4, 4).Value >= 4.1 And Sheets("Selection").Cells(4, 4).Value <= 6 Then
Sheets("Selection").Cells(1, 10).Value = "0.1"
ElseIf Sheets("Selection").Cells(4, 4).Value >= 6.2 And Sheets("Selection").Cells(4, 4).Value <= 10 Then
Sheets("Selection").Cells(1, 10).Value = "0.2"
ElseIf Sheets("Selection").Cells(4, 4).Value >= 10.5 And Sheets("Selection").Cells(4, 4).Value <= 20 Then
Sheets("Selection").Cells(1, 10).Value = "0.5"
ElseIf Sheets("Selection").Cells(4, 4).Value >= 21 And Sheets("Selection").Cells(4, 4).Value <= 30 Then
Sheets("Selection").Cells(1, 10).Value = "1"
ElseIf Sheets("Selection").Cells(4, 4).Value >= 32 And Sheets("Selection").Cells(4, 4).Value <= 50 Then
Sheets("Selection").Cells(1, 10).Value = "2"
ElseIf Sheets("Selection").Cells(4, 4).Value >= 55 And Sheets("Selection").Cells(4, 4).Value <= 100 Then
Sheets("Selection").Cells(1, 10).Value = "5"
ElseIf Sheets("Selection").Cells(4, 4).Value >= 110 And Sheets("Selection").Cells(4, 4).Value <= 1000 Then
Sheets("Selection").Cells(1, 10).Value = "10"
End If 'work out the tick increments

'Start to lay
If Price <> Sheets("Selection").Cells(4, 4).Value And Price <= Sheets("Selection").Cells(4, 4).Value - (Sheets("Selection").Cells(1, 10).Value * Sheets("Selection").Cells(6, 4).Value) And Sheets("Selection").Cells(11, iCol - 1).Value = "start" Then 'if the price changes from last price record and move on to next col

Sheets("Selection").Cells(10, iCol).Value = Sheets("Selection").Cells(4, 4).Value
Sheets("Selection").Cells(11, iCol).Value = "l"
Price = Sheets("Selection").Cells(4, 4).Value
Sheets("Selection").Cells(12, iCol).Value = Sheets("Selection").Cells(5, 4).Value
Sheets("Selection").Cells(13, iCol).Value = Sheets("Selection").Cells(5, 4).Value - Sheets("Selection").Cells(12, iCol - 1).Value
Sheets("Selection").Cells(14, iCol).Value = Sheets("Selection").Cells(13, iCol).Value / Sheets("Selection").Cells(12, iCol).Value
Sheets("Selection").Cells(15, iCol).Value = Sheets("Market").Cells(2, 4).Value
i = i + 1
x = x - 1
Sheets("Renko").Cells(x, i).Interior.ColorIndex = 38
Sheets("Renko").Cells(3, i).Value = Sheets("Selection").Cells(15, iCol).Value
Sheets("Renko").Cells(x, iVolMatch).Value = Sheets("Selection").Cells(16, iCol).Value
Sheets("Renko").Cells(x, iPriceRange).Value = Sheets("Selection").Cells(10, iCol - 1).Value & sep & Price

With Sheets("Renko")
.Range(.Cells(x + 3, i), .Cells(x + (3 + 5), i)).Merge
.Range(.Cells(x + 3, i), .Cells(x + (3 + 5), i)).Orientation = 90
.Range(.Cells(x + 3, i), .Cells(x + (3 + 5), i)).WrapText = False
.Range(.Cells(x + 3, i), .Cells(x + (3 + 5), i)).VerticalAlignment = xlBottom
.Range(.Cells(x + 3, i), .Cells(x + (3 + 5), i)).HorizontalAlignment = xlCenter
.Range(.Cells(x + 3, i), .Cells(x + (3 + 5), i)).NumberFormat = "$#,##0.00_);($#,##0)"
.Range(.Cells(x + 3, i), .Cells(x + (3 + 5), i)).Value = Sheets("Selection").Cells(13, iCol).Value
.Range(.Cells(x + 9, i), .Cells(x + (9 + 2), i)).Merge
.Range(.Cells(x + 9, i), .Cells(x + (9 + 2), i)).Orientation = 90
.Range(.Cells(x + 9, i), .Cells(x + (9 + 2), i)).WrapText = False
.Range(.Cells(x + 9, i), .Cells(x + (9 + 2), i)).VerticalAlignment = xlBottom
.Range(.Cells(x + 9, i), .Cells(x + (9 + 2), i)).HorizontalAlignment = xlCenter
.Range(.Cells(x + 9, i), .Cells(x + (9 + 2), i)).NumberFormat = "0.01%"
.Range(.Cells(x + 9, i), .Cells(x + (9 + 2), i)).ShrinkToFit = True
.Range(.Cells(x + 9, i), .Cells(x + (9 + 2), i)).Value = Sheets("Selection").Cells(14, iCol).Value
If Sheets("Renko").Cells(x + 9, i).Value >= Sheets("Selection").Cells(7, 4).Value Then
Sheets("Renko").Cells(x + 12, i).Interior.ColorIndex = 4
End If
End With

iCol = iCol + 1



'Start to back
ElseIf Price <> Sheets("Selection").Cells(4, 4).Value And Price >= Sheets("Selection").Cells(4, 4).Value + (Sheets("Selection").Cells(1, 10).Value * Sheets("Selection").Cells(6, 4).Value) And Sheets("Selection").Cells(11, iCol - 1).Value = "start" Then 'if the price changes from last price record and move on to next col
Sheets("Selection").Cells(10, iCol).Value = Sheets("Selection").Cells(4, 4).Value
Sheets("Selection").Cells(11, iCol).Value = "b"
Price = Sheets("Selection").Cells(4, 4).Value
Sheets("Selection").Cells(12, iCol).Value = Sheets("Selection").Cells(5, 4).Value
Sheets("Selection").Cells(13, iCol).Value = Sheets("Selection").Cells(5, 4).Value - Sheets("Selection").Cells(12, iCol - 1).Value
Sheets("Selection").Cells(14, iCol).Value = Sheets("Selection").Cells(13, iCol).Value / Sheets("Selection").Cells(12, iCol).Value
Sheets("Selection").Cells(15, iCol).Value = Sheets("Market").Cells(2, 4).Value
i = i + 1
x = x - 1
Sheets("Renko").Cells(x, i).Interior.ColorIndex = 24
Sheets("Renko").Cells(3, i).Value = Sheets("Selection").Cells(15, iCol).Value
Sheets("Renko").Cells(x, iVolMatch).Value = Sheets("Selection").Cells(16, iCol).Value
Sheets("Renko").Cells(x, iPriceRange).Value = Price & sep & Sheets("Selection").Cells(10, iCol - 1).Value

With Sheets("Renko")
.Range(.Cells(x - 3, i), .Cells(x - (3 + 5), i)).Merge
.Range(.Cells(x - 3, i), .Cells(x - (3 + 5), i)).Orientation = 90
.Range(.Cells(x - 3, i), .Cells(x - (3 + 5), i)).WrapText = False
.Range(.Cells(x - 3, i), .Cells(x - (3 + 5), i)).VerticalAlignment = xlBottom
.Range(.Cells(x - 3, i), .Cells(x - (3 + 5), i)).HorizontalAlignment = xlCenter
.Range(.Cells(x - 3, i), .Cells(x - (3 + 5), i)).NumberFormat = "$#,##0.00_);($#,##0)"
.Range(.Cells(x - 3, i), .Cells(x - (3 + 5), i)).Value = Sheets("Selection").Cells(13, iCol).Value
.Range(.Cells(x - 9, i), .Cells(x - (9 + 2), i)).Merge
.Range(.Cells(x - 9, i), .Cells(x - (9 + 2), i)).Orientation = 90
.Range(.Cells(x - 9, i), .Cells(x - (9 + 2), i)).WrapText = False
.Range(.Cells(x - 9, i), .Cells(x - (9 + 2), i)).VerticalAlignment = xlBottom
.Range(.Cells(x - 9, i), .Cells(x - (9 + 2), i)).HorizontalAlignment = xlCenter
.Range(.Cells(x - 9, i), .Cells(x - (9 + 2), i)).NumberFormat = "0.01%"
.Range(.Cells(x - 9, i), .Cells(x - (9 + 2), i)).Value = Sheets("Selection").Cells(14, iCol).Value
If Sheets("Renko").Cells(x - 11, i).Value >= Sheets("Selection").Cells(7, 4).Value Then
Sheets("Renko").Cells(x - 12, i).Interior.ColorIndex = 4
End If
End With
iCol = iCol + 1


'lay to lay
ElseIf Price <> Sheets("Selection").Cells(4, 4).Value And Price <= (Sheets("Selection").Cells(4, 4).Value - (Sheets("Selection").Cells(1, 10).Value * Sheets("Selection").Cells(6, 4).Value)) And Sheets("Selection").Cells(11, iCol - 1).Value = "l" Then 'if the price changes from last price record and move on to next col

Sheets("Selection").Cells(10, iCol).Value = Sheets("Selection").Cells(4, 4).Value
Sheets("Selection").Cells(11, iCol).Value = "l"
Price = Sheets("Selection").Cells(4, 4).Value
Sheets("Selection").Cells(12, iCol).Value = Sheets("Selection").Cells(5, 4).Value
Sheets("Selection").Cells(13, iCol).Value = Sheets("Selection").Cells(5, 4).Value - Sheets("Selection").Cells(12, iCol - 1).Value
Sheets("Selection").Cells(14, iCol).Value = Sheets("Selection").Cells(13, iCol).Value / Sheets("Selection").Cells(12, iCol).Value
Sheets("Selection").Cells(15, iCol).Value = Sheets("Market").Cells(2, 4).Value
i = i + 1
x = x - 1
Sheets("Renko").Cells(x, i).Interior.ColorIndex = 38
Sheets("Renko").Cells(3, i).Value = Sheets("Selection").Cells(15, iCol).Value
Sheets("Renko").Cells(x, iVolMatch).Value = Sheets("Selection").Cells(16, iCol).Value
If Sheets("Renko").Cells(x, iPriceRange).Value = "" Then
Sheets("Renko").Cells(x, iPriceRange).Value = Sheets("Selection").Cells(10, iCol - 1).Value & sep & Price
End If

With Sheets("Renko")
.Range(.Cells(x + 3, i), .Cells(x + (3 + 5), i)).Merge
.Range(.Cells(x + 3, i), .Cells(x + (3 + 5), i)).Orientation = 90
.Range(.Cells(x + 3, i), .Cells(x + (3 + 5), i)).WrapText = False
.Range(.Cells(x + 3, i), .Cells(x + (3 + 5), i)).VerticalAlignment = xlBottom
.Range(.Cells(x + 3, i), .Cells(x + (3 + 5), i)).HorizontalAlignment = xlCenter
.Range(.Cells(x + 3, i), .Cells(x + (3 + 5), i)).NumberFormat = "$#,##0.00_);($#,##0)"
.Range(.Cells(x + 3, i), .Cells(x + (3 + 5), i)).Value = Sheets("Selection").Cells(13, iCol).Value
.Range(.Cells(x + 9, i), .Cells(x + (9 + 2), i)).Merge
.Range(.Cells(x + 9, i), .Cells(x + (9 + 2), i)).Orientation = 90
.Range(.Cells(x + 9, i), .Cells(x + (9 + 2), i)).WrapText = False
.Range(.Cells(x + 9, i), .Cells(x + (9 + 2), i)).VerticalAlignment = xlBottom
.Range(.Cells(x + 9, i), .Cells(x + (9 + 2), i)).HorizontalAlignment = xlCenter
.Range(.Cells(x + 9, i), .Cells(x + (9 + 2), i)).NumberFormat = "0.01%"
.Range(.Cells(x + 9, i), .Cells(x + (9 + 2), i)).Value = Sheets("Selection").Cells(14, iCol).Value
If Sheets("Renko").Cells(x + 9, i).Value >= Sheets("Selection").Cells(7, 4).Value Then
Sheets("Renko").Cells(x + 12, i).Interior.ColorIndex = 4
End If
End With
iCol = iCol + 1



'back to back
ElseIf Price <> Sheets("Selection").Cells(4, 4).Value And Price >= (Sheets("Selection").Cells(4, 4).Value + (Sheets("Selection").Cells(1, 10).Value * Sheets("Selection").Cells(6, 4).Value)) And Sheets("Selection").Cells(11, iCol - 1).Value = "b" Then 'if the price changes from last price record and move on to next col
Sheets("Selection").Cells(10, iCol).Value = Sheets("Selection").Cells(4, 4).Value
Sheets("Selection").Cells(11, iCol).Value = "b"
Price = Sheets("Selection").Cells(4, 4).Value
Sheets("Selection").Cells(12, iCol).Value = Sheets("Selection").Cells(5, 4).Value
Sheets("Selection").Cells(13, iCol).Value = Sheets("Selection").Cells(5, 4).Value - Sheets("Selection").Cells(12, iCol - 1).Value
Sheets("Selection").Cells(14, iCol).Value = Sheets("Selection").Cells(13, iCol).Value / Sheets("Selection").Cells(12, iCol).Value
Sheets("Selection").Cells(15, iCol).Value = Sheets("Market").Cells(2, 4).Value
i = i + 1
x = x + 1
Sheets("Renko").Cells(x, i).Interior.ColorIndex = 24
Sheets("Renko").Cells(3, i).Value = Sheets("Selection").Cells(15, iCol).Value
Sheets("Renko").Cells(x, iVolMatch).Value = Sheets("Selection").Cells(16, iCol).Value
If Sheets("Renko").Cells(x, iPriceRange).Value = "" Then
Sheets("Renko").Cells(x, iPriceRange).Value = Price & sep & Sheets("Selection").Cells(10, iCol - 1).Value
End If

With Sheets("Renko")
.Range(.Cells(x - 3, i), .Cells(x - (3 + 5), i)).Merge
.Range(.Cells(x - 3, i), .Cells(x - (3 + 5), i)).Orientation = 90
.Range(.Cells(x - 3, i), .Cells(x - (3 + 5), i)).WrapText = False
.Range(.Cells(x - 3, i), .Cells(x - (3 + 5), i)).VerticalAlignment = xlBottom
.Range(.Cells(x - 3, i), .Cells(x - (3 + 5), i)).HorizontalAlignment = xlCenter
.Range(.Cells(x - 3, i), .Cells(x - (3 + 5), i)).NumberFormat = "$#,##0.00_);($#,##0)"
.Range(.Cells(x - 3, i), .Cells(x - (3 + 5), i)).Value = Sheets("Selection").Cells(13, iCol).Value
.Range(.Cells(x - 9, i), .Cells(x - (9 + 2), i)).Merge
.Range(.Cells(x - 9, i), .Cells(x - (9 + 2), i)).Orientation = 90
.Range(.Cells(x - 9, i), .Cells(x - (9 + 2), i)).WrapText = False
.Range(.Cells(x - 9, i), .Cells(x - (9 + 2), i)).VerticalAlignment = xlBottom
.Range(.Cells(x - 9, i), .Cells(x - (9 + 2), i)).HorizontalAlignment = xlCenter
.Range(.Cells(x - 9, i), .Cells(x - (9 + 2), i)).NumberFormat = "0.01%"
.Range(.Cells(x - 9, i), .Cells(x - (9 + 2), i)).Value = Sheets("Selection").Cells(14, iCol).Value
If Sheets("Renko").Cells(x - 11, i).Value >= Sheets("Selection").Cells(7, 4).Value Then
Sheets("Renko").Cells(x - 12, i).Interior.ColorIndex = 4
End If
End With
iCol = iCol + 1


'back to lay

ElseIf Price <> Sheets("Selection").Cells(4, 4).Value And Price <= (Sheets("Selection").Cells(4, 4).Value - ((Sheets("Selection").Cells(1, 10).Value * Sheets("Selection").Cells(6, 4).Value) * 2)) And Sheets("Selection").Cells(11, iCol - 1).Value = "b" Then 'if the price changes from last price record and move on to next col

Sheets("Selection").Cells(10, iCol).Value = Sheets("Selection").Cells(4, 4).Value
Sheets("Selection").Cells(11, iCol).Value = "l"
Price = Sheets("Selection").Cells(4, 4).Value
Sheets("Selection").Cells(12, iCol).Value = Sheets("Selection").Cells(5, 4).Value
Sheets("Selection").Cells(13, iCol).Value = Sheets("Selection").Cells(5, 4).Value - Sheets("Selection").Cells(12, iCol - 1).Value
Sheets("Selection").Cells(14, iCol).Value = Sheets("Selection").Cells(13, iCol).Value / Sheets("Selection").Cells(12, iCol).Value
Sheets("Selection").Cells(15, iCol).Value = Sheets("Market").Cells(2, 4).Value
i = i + 1
x = x - 1
Sheets("Renko").Cells(x, i).Interior.ColorIndex = 38
Sheets("Renko").Cells(3, i).Value = Sheets("Selection").Cells(15, iCol).Value
Sheets("Renko").Cells(x, iVolMatch).Value = Sheets("Selection").Cells(16, iCol).Value
If Sheets("Renko").Cells(x, iPriceRange).Value = "" Then
Sheets("Renko").Cells(x, iPriceRange).Value = (Price - Sheets("Selection").Cells(6, 4).Value * Sheets("Selection").Cells(1, 10).Value) & sep & Price
End If
With Sheets("Renko")
.Range(.Cells(x + 3, i), .Cells(x + (3 + 5), i)).Merge
.Range(.Cells(x + 3, i), .Cells(x + (3 + 5), i)).Orientation = 90
.Range(.Cells(x + 3, i), .Cells(x + (3 + 5), i)).WrapText = False
.Range(.Cells(x + 3, i), .Cells(x + (3 + 5), i)).VerticalAlignment = xlBottom
.Range(.Cells(x + 3, i), .Cells(x + (3 + 5), i)).HorizontalAlignment = xlCenter
.Range(.Cells(x + 3, i), .Cells(x + (3 + 5), i)).NumberFormat = "$#,##0.00_);($#,##0)"
.Range(.Cells(x + 3, i), .Cells(x + (3 + 5), i)).Value = Sheets("Selection").Cells(13, iCol).Value
.Range(.Cells(x + 9, i), .Cells(x + (9 + 2), i)).Merge
.Range(.Cells(x + 9, i), .Cells(x + (9 + 2), i)).Orientation = 90
.Range(.Cells(x + 9, i), .Cells(x + (9 + 2), i)).WrapText = False
.Range(.Cells(x + 9, i), .Cells(x + (9 + 2), i)).VerticalAlignment = xlBottom
.Range(.Cells(x + 9, i), .Cells(x + (9 + 2), i)).HorizontalAlignment = xlCenter
.Range(.Cells(x + 9, i), .Cells(x + (9 + 2), i)).NumberFormat = "0.01%"
.Range(.Cells(x + 9, i), .Cells(x + (9 + 2), i)).Value = Sheets("Selection").Cells(14, iCol).Value
If Sheets("Renko").Cells(x + 9, i).Value >= Sheets("Selection").Cells(7, 4).Value Then
Sheets("Renko").Cells(x + 12, i).Interior.ColorIndex = 4
End If
End With
iCol = iCol + 1


'lay to back

ElseIf Price <> Sheets("Selection").Cells(4, 4).Value And Price >= (Sheets("Selection").Cells(4, 4).Value + ((Sheets("Selection").Cells(1, 10).Value * Sheets("Selection").Cells(6, 4).Value) * 2)) And Sheets("Selection").Cells(11, iCol - 1).Value = "l" Then 'if the price changes from last price record and move on to next col
Sheets("Selection").Cells(10, iCol).Value = Sheets("Selection").Cells(4, 4).Value
Sheets("Selection").Cells(11, iCol).Value = "b"
Price = Sheets("Selection").Cells(4, 4).Value
Sheets("Selection").Cells(12, iCol).Value = Sheets("Selection").Cells(5, 4).Value
Sheets("Selection").Cells(13, iCol).Value = Sheets("Selection").Cells(5, 4).Value - Sheets("Selection").Cells(12, iCol - 1).Value
Sheets("Selection").Cells(14, iCol).Value = Sheets("Selection").Cells(13, iCol).Value / Sheets("Selection").Cells(12, iCol).Value
Sheets("Selection").Cells(15, iCol).Value = Sheets("Market").Cells(2, 4).Value
i = i + 1
x = x + 1
Sheets("Renko").Cells(x, i).Interior.ColorIndex = 24
Sheets("Renko").Cells(3, i).Value = Sheets("Selection").Cells(15, iCol).Value
Sheets("Renko").Cells(x, iVolMatch).Value = Sheets("Selection").Cells(16, iCol).Value
If Sheets("Renko").Cells(x, iPriceRange).Value = "" Then
Sheets("Renko").Cells(x, iPriceRange).Value = Price & sep & (Price + Sheets("Selection").Cells(6, 4).Value * Sheets("Selection").Cells(1, 10).Value)
End If
With Sheets("Renko")
.Range(.Cells(x - 3, i), .Cells(x - (3 + 5), i)).Merge
.Range(.Cells(x - 3, i), .Cells(x - (3 + 5), i)).Orientation = 90
.Range(.Cells(x - 3, i), .Cells(x - (3 + 5), i)).WrapText = False
.Range(.Cells(x - 3, i), .Cells(x - (3 + 5), i)).VerticalAlignment = xlBottom
.Range(.Cells(x - 3, i), .Cells(x - (3 + 5), i)).HorizontalAlignment = xlCenter
.Range(.Cells(x - 3, i), .Cells(x - (3 + 5), i)).NumberFormat = "$#,##0.00_);($#,##0)"
.Range(.Cells(x - 3, i), .Cells(x - (3 + 5), i)).Value = Sheets("Selection").Cells(13, iCol).Value
.Range(.Cells(x - 9, i), .Cells(x - (9 + 2), i)).Merge
.Range(.Cells(x - 9, i), .Cells(x - (9 + 2), i)).Orientation = 90
.Range(.Cells(x - 9, i), .Cells(x - (9 + 2), i)).WrapText = False
.Range(.Cells(x - 9, i), .Cells(x - (9 + 2), i)).VerticalAlignment = xlBottom
.Range(.Cells(x - 9, i), .Cells(x - (9 + 2), i)).HorizontalAlignment = xlCenter
.Range(.Cells(x - 9, i), .Cells(x - (9 + 2), i)).NumberFormat = "0.01%"
.Range(.Cells(x - 9, i), .Cells(x - (9 + 2), i)).Value = Sheets("Selection").Cells(14, iCol).Value
If Sheets("Renko").Cells(x - 11, i).Value >= Sheets("Selection").Cells(7, 4).Value Then
Sheets("Renko").Cells(x - 12, i).Interior.ColorIndex = 4
End If
End With
iCol = iCol + 1



End If

End If




Application.EnableEvents = True




End Sub

at present on the auto recording sheet im making if i code anything into sheet named selection, nothing happens on the shete its as if the code is not working at all, only things written within sheet called Market work

[Captain Sensible]

When you link BA to excel it will only update the sheet it's sending prices to. Because it sends data like times etc that means every time something on that sheet is physically changed it will trigger a worksheet_change event and run the code within.

So if nothing is changed on the other sheet there is nothing to trigger a Worksheet_change event. When you say you designate two cells in sheet selection if they don't change again the sheet won't trigger, if those cells are updated by some formula like say =Sheet2!A1 that also wouldn't trigger a worksheet change event as nothing in the formula has changed. To trigger code where a formula has changed you'd need to use worksheet_calculate which triggers when a calculation has been done.

In general there is very little point and usually no need whatsoever to have Worksheet_change/calculate also running on a separate sheet as any changes on the main sheet can easily kick off routine to amend all sheets on the workbook. You'll just complicate things having worksheet_calculates on each sheet when you could do it much easier just by sticking the code in modules.

[cjones198]

yeah, I was just being lazy, trying to just copy and paste codes, but I'm sticking it in linked to market sheet now.

I cant see how sticking stuff in modules will aid my sheet here, except

race changes so

call save former chart and code and log into relevant sheets (module)
call delete former informations and clear rows etc (module)
call save start price, start amount of money matched, start time etc (module)

then its just live recordings plotting depending on how many ticks i designate in a cell somehwere. (worksheet event change)
call q2 to enter -1 when inplay (worksheet event change)

[Captain Sensible]

Also the top of you code is

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)



If Price = Empty Then 'If no start price recorded dont do anything
Exit Sub
End If

And Price doesn't seem to be declared anywhere so it will always be empty and exit your routine

[Captain Sensible]

yeah, I was just being lazy, trying to just copy and paste codes, but I'm sticking it in linked to market sheet now.

I cant see how sticking stuff in modules will aid my sheet here, except

If you stick all your code under the worksheet_calculate on market it will get very big and messy and hard to change or even track down errors. When code is in modules it's very easy to run those modules separately from the macro menu so you can see that they do what you exepct without the hassle of linking everything to BA. You can simply have the sheet not connected change a price in F5 or whatever and run the code manually to see it works.

Might seem a pain to stick things in modules and use public variables but if you start doing more sheets it's a godsend to simply copy over set routines that aren't hidden within masses of code that can be used anywhere and not need recoding. Plus sticking the code you have under the selections sheet is no harder to paste in a module than have it under the sheet where it doesn't work
.

[cjones198]

Thanks Captain!

Price is declared in a commandclick() button i made on the sheet. you have to click that button to start recording

[Captain Sensible]

Unless it's declared as a global variable by your commandclick button that worksheet_calculate routine won't have access to the value each time it runs. Just stick Option Explicit t the top and it will throw an error is price isn't available

Code: Select all

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)



If Price = Empty Then 'If no start price recorded dont do anything
Exit Sub
End If


....................etttc

[cjones198]

thanks Captain!

[cjones198]

Sorry to be a meither Captain.

That big long list of tick codes. You sent on this thread do I just stick all of that into one module? And say call it "tick_module"
Then when I wanna have the sheet only log 2 ticks? Or whatever how do I call it?
But it is important the direction the ticks occur. If the price goes up 2 ticks its "l"
If it goes down 2 its "b"

[cjones198]

It looks like this

Price
2.50 - 2.54 | 2.54 - 2.58|2.58 - 2.62|2.62 - 2.54|
"l" "l" "l" "b"

The last is a "b" using 4 ticks because previous is an "l"

2.50 - 2.46 | 2.46 - 2.42 |2.42 - 2.38|2.38 - 2.46
"b" "b" "b" "l"
The last is an "l" using 4 ticks because previous is an "b"
If new price is an "l" and previous is also "l" only log 2 ticks
If new price is "b" and previous is also "b" only log 2 ticks

[Captain Sensible]

Sorry to be a meither Captain.

That big long list of tick codes. You sent on this thread do I just stick all of that into one module? And say call it "tick_module"
Then when I wanna have the sheet only log 2 ticks? Or whatever how do I call it?
But it is important the direction the ticks occur. If the price goes up 2 ticks its "l"
If it goes down 2 its "b"

Yes just stick the lot into a module doesn't matter what it's called but tick_module is fine as it mean you can easily identify it to export to any other sheets you use.

getTicks will simply get the difference in ticks between two prices so =getticks(2.5,2.54) will give us a value of 2, =getticks(2.54,2.50) gives us a value of -2

if you want to log the direction you use things like case or even If/elseif statements like

If getTicks(old, new) = 2 Then
something is "l"
ElseIf getTicks(old, new) = -2 Then
something is "b"
End If


Not sure what you mean by the last statements about things being 4 ticks when you say you only want to log 2 ticks, think you'd just have to figure that out with a few elseif's if it's not simple logic, plus you're bound to have situations where a price may move 3 ticks in one refresh