#### magiciansmask

##### New Member

- Joined
- Nov 15, 2019

- Messages
- 10

I found a sheet online and would love to make my own. The problem is, I'm not sure how one thing occurs. When you enter the date on column row A15, it opens up other rows as well. How is this done?

This is what was in the visual basic

Private Sub worksheet_change(ByVal Target As Excel.Range)

Dim numBets As String

Dim numCharsInBet As Long

Dim numCharsPrevLocation As Long

Dim MyBet(1) As String

Dim MyWon(4) As String

On Error GoTo myError

If Target.Column < 12 Then

If Target.Row > 14 Then

If Target.Column = 1 Then

MyBet(0) = ""

MyBet(1) = "BET"

MyWon(0) = "Won"

MyWon(1) = "Lost"

With Range("Z" & Target.Row).Validation

.Delete

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _

Operator:=xlValidateList, Formula1:=Join(MyBet, ",")

'.IgnoreBlank = True

.InCellDropdown = True

.InputTitle = ""

.ErrorTitle = ""

.InputMessage = ""

.ErrorMessage = ""

.ShowInput = True

.ShowError = True

End With

With Range("K" & Target.Row).Validation

.Delete

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _

Operator:=xlValidateList, Formula1:=Join(MyWon, ",")

.IgnoreBlank = True

.InCellDropdown = True

.InputTitle = ""

.ErrorTitle = ""

.InputMessage = ""

.ErrorMessage = ""

.ShowInput = True

.ShowError = True

End With

If IsDate(Target.Value) Then

If Target.Value > Cells(Target.Row - 1, Target.Column).Value Then

If Target.Row > 15 And Range("E11").Value <> "" Then

'Adding count to previous row

numBets = Range("E11").Value

numCharsInBet = Len(numBets)

numCharsPrevLocation = Len(Cells(Target.Row - 1, Target.Column + 1).Value)

With Cells(Target.Row - 1, Target.Column + 1)

.Value = .Value & " " & numBets

With .Characters(Start:=numCharsPrevLocation + 1, Length:=numCharsPrevLocation + numCharsInBet).Font

.Color = -16776961

End With

End With

'Putting border

With Range(Cells(Target.Row, 1), Cells(Target.Row, 40)).Borders(xlEdgeTop)

.LineStyle = xlContinuous

.Weight = xlMedium

End With

End If

Range("E11").Value = Range("E11").Value + 1

Else

If Target.Row = 15 Then

Range("E11").Value = 1

End If

End If

End If

Range("N" & Target.Row).NumberFormat = "$#,##0.00"

Range("Q" & Target.Row).NumberFormat = "$#,##0.00"

Range("S" & Target.Row).NumberFormat = "$#,##0.00"

Range("L" & Target.Row).Formula = "=IF(K" & Target.Row & "=" & """" & "Won" & """" & ", " & """" & "Bet Win" & """" & "," & """" & "Bet Lost" & """" & ")" '"=IF(K" & Target.Row & "=" & """" & "Won" & """" & ",IF(I" & Target.Row & "=" & """" & "BET" & """" & "," & """" & "BW" & """" & ",IF(I" & Target.Row & "=" & """" & "" & """" & "," & """" & "LOST" & """" & "," & """" & "G/T" & """" & ")),IF(I" & Target.Row & "=" & """" & "BET" & """" & "," & """" & "LOST" & """" & ",IF(I" & Target.Row & "=" & """" & "" & """" & "," & """" & "LW" & """" & "," & """" & "G/T" & """" & ")))"

Range("N" & Target.Row).Formula = "=IF(Z" & Target.Row & "=" & """" & "TRADE" & """" & ",J" & Target.Row & ",IF(I" & Target.Row & "=" & """" & "GREEN" & """" & ",J" & Target.Row & ",IF(E" & Target.Row & ">0,IF(K" & Target.Row & "=" & """" & "WON" & """" & ",(E" & Target.Row & "*G" & Target.Row & "-E" & Target.Row & ")*(1-M" & Target.Row & "),-E" & Target.Row & "),IF(F" & Target.Row & ">0,IF(K" & Target.Row & "=" & """" & "WON" & """" & ",-F" & Target.Row & "*H" & Target.Row & "+F" & Target.Row & ",F" & Target.Row & "*(1-M" & Target.Row & "))))))"

If Target.Row > 15 Then

Range("O" & Target.Row).Formula = "= O" & Target.Row - 1 & " + N" & Target.Row

Else

Range("O" & Target.Row).Formula = "=N" & Target.Row

End If

End If

If Range("N" & Target.Row).Value >= 0 Then

Range("N" & Target.Row).Font.Color = vbBlack

Range("Q" & Target.Row).Formula = "=N" & Target.Row

Range("Q" & Target.Row).Font.Color = vbBlack

Range("S" & Target.Row).Formula = ""

Else

Range("N" & Target.Row).Font.Color = vbRed

Range("S" & Target.Row).Font.Color = vbRed

Range("S" & Target.Row).Formula = "=N" & Target.Row

Range("Q" & Target.Row).Formula = ""

End If

If UCase(Range("K" & Target.Row).Value) <> UCase("Won") Then

Range("N" & Target.Row).Font.Color = vbRed

Else

Range("N" & Target.Row).Font.Color = vbBlack

End If

Range("Z13").Formula = "=O" & Target.Row & "/e11"

End If

End If

GoTo exiter

myError:

GoTo exiter

exiter:

End Sub

This is what was in the visual basic

Private Sub worksheet_change(ByVal Target As Excel.Range)

Dim numBets As String

Dim numCharsInBet As Long

Dim numCharsPrevLocation As Long

Dim MyBet(1) As String

Dim MyWon(4) As String

On Error GoTo myError

If Target.Column < 12 Then

If Target.Row > 14 Then

If Target.Column = 1 Then

MyBet(0) = ""

MyBet(1) = "BET"

MyWon(0) = "Won"

MyWon(1) = "Lost"

With Range("Z" & Target.Row).Validation

.Delete

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _

Operator:=xlValidateList, Formula1:=Join(MyBet, ",")

'.IgnoreBlank = True

.InCellDropdown = True

.InputTitle = ""

.ErrorTitle = ""

.InputMessage = ""

.ErrorMessage = ""

.ShowInput = True

.ShowError = True

End With

With Range("K" & Target.Row).Validation

.Delete

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _

Operator:=xlValidateList, Formula1:=Join(MyWon, ",")

.IgnoreBlank = True

.InCellDropdown = True

.InputTitle = ""

.ErrorTitle = ""

.InputMessage = ""

.ErrorMessage = ""

.ShowInput = True

.ShowError = True

End With

If IsDate(Target.Value) Then

If Target.Value > Cells(Target.Row - 1, Target.Column).Value Then

If Target.Row > 15 And Range("E11").Value <> "" Then

'Adding count to previous row

numBets = Range("E11").Value

numCharsInBet = Len(numBets)

numCharsPrevLocation = Len(Cells(Target.Row - 1, Target.Column + 1).Value)

With Cells(Target.Row - 1, Target.Column + 1)

.Value = .Value & " " & numBets

With .Characters(Start:=numCharsPrevLocation + 1, Length:=numCharsPrevLocation + numCharsInBet).Font

.Color = -16776961

End With

End With

'Putting border

With Range(Cells(Target.Row, 1), Cells(Target.Row, 40)).Borders(xlEdgeTop)

.LineStyle = xlContinuous

.Weight = xlMedium

End With

End If

Range("E11").Value = Range("E11").Value + 1

Else

If Target.Row = 15 Then

Range("E11").Value = 1

End If

End If

End If

Range("N" & Target.Row).NumberFormat = "$#,##0.00"

Range("Q" & Target.Row).NumberFormat = "$#,##0.00"

Range("S" & Target.Row).NumberFormat = "$#,##0.00"

Range("L" & Target.Row).Formula = "=IF(K" & Target.Row & "=" & """" & "Won" & """" & ", " & """" & "Bet Win" & """" & "," & """" & "Bet Lost" & """" & ")" '"=IF(K" & Target.Row & "=" & """" & "Won" & """" & ",IF(I" & Target.Row & "=" & """" & "BET" & """" & "," & """" & "BW" & """" & ",IF(I" & Target.Row & "=" & """" & "" & """" & "," & """" & "LOST" & """" & "," & """" & "G/T" & """" & ")),IF(I" & Target.Row & "=" & """" & "BET" & """" & "," & """" & "LOST" & """" & ",IF(I" & Target.Row & "=" & """" & "" & """" & "," & """" & "LW" & """" & "," & """" & "G/T" & """" & ")))"

Range("N" & Target.Row).Formula = "=IF(Z" & Target.Row & "=" & """" & "TRADE" & """" & ",J" & Target.Row & ",IF(I" & Target.Row & "=" & """" & "GREEN" & """" & ",J" & Target.Row & ",IF(E" & Target.Row & ">0,IF(K" & Target.Row & "=" & """" & "WON" & """" & ",(E" & Target.Row & "*G" & Target.Row & "-E" & Target.Row & ")*(1-M" & Target.Row & "),-E" & Target.Row & "),IF(F" & Target.Row & ">0,IF(K" & Target.Row & "=" & """" & "WON" & """" & ",-F" & Target.Row & "*H" & Target.Row & "+F" & Target.Row & ",F" & Target.Row & "*(1-M" & Target.Row & "))))))"

If Target.Row > 15 Then

Range("O" & Target.Row).Formula = "= O" & Target.Row - 1 & " + N" & Target.Row

Else

Range("O" & Target.Row).Formula = "=N" & Target.Row

End If

End If

If Range("N" & Target.Row).Value >= 0 Then

Range("N" & Target.Row).Font.Color = vbBlack

Range("Q" & Target.Row).Formula = "=N" & Target.Row

Range("Q" & Target.Row).Font.Color = vbBlack

Range("S" & Target.Row).Formula = ""

Else

Range("N" & Target.Row).Font.Color = vbRed

Range("S" & Target.Row).Font.Color = vbRed

Range("S" & Target.Row).Formula = "=N" & Target.Row

Range("Q" & Target.Row).Formula = ""

End If

If UCase(Range("K" & Target.Row).Value) <> UCase("Won") Then

Range("N" & Target.Row).Font.Color = vbRed

Else

Range("N" & Target.Row).Font.Color = vbBlack

End If

Range("Z13").Formula = "=O" & Target.Row & "/e11"

End If

End If

GoTo exiter

myError:

GoTo exiter

exiter:

End Sub