Hi,
I have the following code but it keeps on stopping at myHomeAwayCol = 18 and I am not sure why. Doe anyone have any ideas?
Cheers,
Phil
[Added code tags~VP]
I have the following code but it keeps on stopping at myHomeAwayCol = 18 and I am not sure why. Doe anyone have any ideas?
Cheers,
Phil
Code:
Sub PhilsCode2()
ChDir "E:\Football Betting"
Workbooks.Open Filename:="E:\Football Betting\Football Results.xls"
Windows("Belgium - Jupiler League.xls").Activate
Sheets("2007-2008").Select
Range("A3:AE308").Select
Range("AE308").Activate
Selection.Interior.ColorIndex = xlNone
Selection.Font.Bold = False
On Error GoTo myEnd
Dim myRow As Long
Dim myValue As String
myHomeAwayCol = 13
myGoalsCol = 14
myAll = 31
myRow = 3
For myRow = 3 To 308 'number of rows to look @
Select Case Cells(myRow, myHomeAwayCol)
Case "H"
If Cells(myRow, myGoalsCol).Value >= 2.5 And Cells(myRow, myAll).Value = "HHH" Then
Call myHighlight(myRow)
End If
If Cells(myRow, myGoalsCol).Value >= 1 And Cells(myRow, myGoalsCol).Value <= 2 And Cells(myRow, myAll).Value = "HHH" Then
Call myHighlight(myRow)
End If
Case "A"
If Cells(myRow, myGoalsCol).Value >= -1.5 And Cells(myRow, myGoalsCol).Value <= 0 And Cells(myRow, myAll).Value = "AAA" Then
Call myHighlight(myRow)
End If
End Select
Next
On Error GoTo myEnd
myHomeAwayCol = 18
myGoalsCol = 19
myAll = 31
myRow = 3
For myRow = 3 To 308 'number of rows to look @
Select Case Cells(myRow, myHomeAwayCol)
Case "H"
If Cells(myRow, myGoalsCol).Value >= 2.5 And Cells(myRow, myAll).Value = "HHH" Then
Call myHighlight1(myRow)
End If
If Cells(myRow, myGoalsCol).Value >= 1.5 And Cells(myRow, myGoalsCol).Value <= 2 And Cells(myRow, myAll).Value = "HHH" Then
Call myHighlight1(myRow)
End If
If Cells(myRow, myGoalsCol).Value >= 0 And Cells(myRow, myGoalsCol).Value <= 0.5 And Cells(myRow, myAll).Value = "HHH" Then
Call myHighlight1(myRow)
End If
Case "D"
If Cells(myRow, myGoalsCol).Value >= 0 And Cells(myRow, myGoalsCol).Value <= 0.5 And Cells(myRow, myAll).Value = "DDD" Or "ADD" Or "AAD" Then
Call myHighlight1(myRow)
End If
Case "A"
If Cells(myRow, myGoalsCol).Value >= 1.5 And Cells(myRow, myGoalsCol).Value <= 2 And Cells(myRow, myAll).Value = "AAA" Then
Call myHighlight1(myRow)
End If
If Cells(myRow, myGoalsCol).Value >= -1.5 And Cells(myRow, myGoalsCol).Value <= 0 And Cells(myRow, myAll).Value = "AAA" Then
Call myHighlight1(myRow)
End If
End Select
Next
On Error GoTo myEnd
myHomeAwayCol = 23
myGoalsCol = 24
myAll = 31
myRow = 3
For myRow = 3 To 308 'number of rows to look @
Select Case Cells(myRow, myHomeAwayCol)
Case "H"
If Cells(myRow, myGoalsCol).Value >= 0 And Cells(myRow, myGoalsCol).Value <= 0.5 And Cells(myRow, myAll).Value = "HHH" Then
Call myHighlight2(myRow)
End If
Case "D"
If Cells(myRow, myGoalsCol).Value >= -1.5 And Cells(myRow, myGoalsCol).Value <= -1 And Cells(myRow, myAll).Value = "DDD" Or "ADD" Or "AAD" Then
Call myHighlight2(myRow)
End If
Case "A"
If Cells(myRow, myGoalsCol).Value >= 2.5 And Cells(myRow, myAll).Value = "AAA" Then
Call myHighlight2(myRow)
End If
If Cells(myRow, myGoalsCol).Value <= -1.5 And Cells(myRow, myAll).Value = "AAA" Then
Call myHighlight2(myRow)
End If
If Cells(myRow, myGoalsCol).Value >= -1 And Cells(myRow, myGoalsCol).Value <= 0 And Cells(myRow, myAll).Value = "AAA" Then
Call myHighlight2(myRow)
End If
End Select
Next
myEnd:
End Sub
Function myHighlight(myRow As Long)
Range("A" & myRow & ":C" & myRow).Select
With Selection.Interior
.ColorIndex = 8
.PatternColorIndex = xlAutomatic
Selection.Font.Bold = True
End With
Range("J" & myRow & ":N" & myRow).Select
With Selection.Interior
.ColorIndex = 8
.PatternColorIndex = xlAutomatic
Selection.Font.Bold = True
End With
Range("AE" & myRow).Select
With Selection.Interior
.ColorIndex = 8
.PatternColorIndex = xlAutomatic
Selection.Font.Bold = True
End With
End Function
Function myHighlight1(myRow As Long)
Range("A" & myRow & ":C" & myRow).Select
With Selection.Interior
.ColorIndex = 8
.PatternColorIndex = xlAutomatic
Selection.Font.Bold = True
End With
Range("O" & myRow & ":S" & myRow).Select
With Selection.Interior
.ColorIndex = 8
.PatternColorIndex = xlAutomatic
Selection.Font.Bold = True
End With
Range("AE" & myRow).Select
With Selection.Interior
.ColorIndex = 8
.PatternColorIndex = xlAutomatic
Selection.Font.Bold = True
End With
End Function
Function myHighlight2(myRow As Long)
Range("A" & myRow & ":C" & myRow).Select
With Selection.Interior
.ColorIndex = 8
.PatternColorIndex = xlAutomatic
Selection.Font.Bold = True
End With
Range("T" & myRow & ":X" & myRow).Select
With Selection.Interior
.ColorIndex = 8
.PatternColorIndex = xlAutomatic
Selection.Font.Bold = True
End With
Range("AE" & myRow).Select
With Selection.Interior
.ColorIndex = 8
.PatternColorIndex = xlAutomatic
Selection.Font.Bold = True
End With
End Function
[Added code tags~VP]