alan myers
Board Regular
- Joined
- Oct 31, 2017
- Messages
- 119
- Office Version
- 365
- Platform
- Windows
VBA Code:
Sub FRANCHISEBALL()
'*************************
' color background
'*************************'
Cells.Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.PatternTintAndShade = 0
End With
With Selection.Font
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With
'*************************
' rename hearders
'*************************
Range("A1").Select
ActiveCell.FormulaR1C1 = "Main/RLINK"
Range("B1").Select
ActiveCell.FormulaR1C1 = "NAME"
Range("C1").Select
ActiveCell.FormulaR1C1 = "TEAM"
Range("G1").Select
ActiveCell.FormulaR1C1 = "THROWS"
Range("M1").Select
ActiveCell.FormulaR1C1 = "SEASON/AB"
Range("N1").Select
ActiveCell.FormulaR1C1 = "HITS"
Range("O1").Select
ActiveCell.FormulaR1C1 = "AVG"
Range("P1").Select
ActiveCell.FormulaR1C1 = "RBI"
Range("Q1").Select
ActiveCell.FormulaR1C1 = "SLG"
Range("R1").Select
ActiveCell.FormulaR1C1 = "2B"
Range("S1").Select
ActiveCell.FormulaR1C1 = "3B"
Range("T1").Select
ActiveCell.FormulaR1C1 = "HR"
Range("U1").Select
ActiveCell.FormulaR1C1 = "BB"
Range("V1").Select
ActiveCell.FormulaR1C1 = "RUNS"
Range("W1").Select
ActiveCell.FormulaR1C1 = "TB"
Range("X1").Select
ActiveCell.FormulaR1C1 = "SB"
Range("Y1").Select
ActiveCell.FormulaR1C1 = "SB%"
Range("Z1").Select
ActiveCell.FormulaR1C1 = "OBP"
Range("AA1").Select
ActiveCell.FormulaR1C1 = "OPS"
Range("AB1").Select
ActiveCell.FormulaR1C1 = "XBH"
Range("AC1").Select
ActiveCell.FormulaR1C1 = "AB/HR"
Range("AD1").Select
ActiveCell.FormulaR1C1 = "ISO"
Range("AE1").Select
ActiveCell.FormulaR1C1 = "RC"
Range("AF1").Select
ActiveCell.FormulaR1C1 = "E"
Range("AG1").Select
ActiveCell.FormulaR1C1 = "CAREER/SEASONS"
Range("AH1").Select
ActiveCell.FormulaR1C1 = "TEAM"
Range("AI1").Select
ActiveCell.FormulaR1C1 = "AB"
Range("AJ1").Select
ActiveCell.FormulaR1C1 = "HITS"
Range("AK1").Select
ActiveCell.FormulaR1C1 = "AVG"
Range("AL1").Select
ActiveCell.FormulaR1C1 = "RBI"
Range("AM1").Select
ActiveCell.FormulaR1C1 = "SLG"
Range("AN1").Select
ActiveCell.FormulaR1C1 = "2B"
Range("AO1").Select
ActiveCell.FormulaR1C1 = "3B"
Range("AP1").Select
ActiveCell.FormulaR1C1 = "HR"
Range("AQ1").Select
ActiveCell.FormulaR1C1 = "SB"
Range("AR1").Select
ActiveCell.FormulaR1C1 = "MINORS/AB"
Range("AS1").Select
ActiveCell.FormulaR1C1 = "HITS"
Range("AT1").Select
ActiveCell.FormulaR1C1 = "AVG"
Range("AU1").Select
ActiveCell.FormulaR1C1 = "RBI"
Range("AV1").Select
ActiveCell.FormulaR1C1 = "SLG"
Range("AW1").Select
ActiveCell.FormulaR1C1 = "2B"
Range("AX1").Select
ActiveCell.FormulaR1C1 = "3B"
Range("AY1").Select
ActiveCell.FormulaR1C1 = "HR"
Range("AZ1").Select
ActiveCell.FormulaR1C1 = "BB"
Range("BA1").Select
ActiveCell.FormulaR1C1 = "TB"
Range("BB1").Select
ActiveCell.FormulaR1C1 = "PA"
Range("BC1").Select
ActiveCell.FormulaR1C1 = "OBP"
Range("BD1").Select
ActiveCell.FormulaR1C1 = "OPS"
Range("BE1").Select
ActiveCell.FormulaR1C1 = "BB/PA"
Range("BF1").Select
ActiveCell.FormulaR1C1 = "XBH"
Range("BG1").Select
ActiveCell.FormulaR1C1 = "AB/HR"
Range("BH1").Select
ActiveCell.FormulaR1C1 = "ISO"
Range("BI1").Select
ActiveCell.FormulaR1C1 = "RC"
Range("BJ1").Select
ActiveCell.FormulaR1C1 = "SCOUT PIT/CONTROL"
Range("BK1").Select
ActiveCell.FormulaR1C1 = "MOVEMENT"
Range("BH8:BJ16").Select
Range("BL1").Select
ActiveCell.FormulaR1C1 = "VELOCITY"
Range("BM1").Select
ActiveCell.FormulaR1C1 = "STAMINA"
Range("BN1").Select
ActiveCell.FormulaR1C1 = "POWER"
Range("BO1").Select
ActiveCell.FormulaR1C1 = "SCOUTH-CONTACT"
Range("BN1").Select
ActiveCell.FormulaR1C1 = "SCOUT HIT/POWER"
Range("BO1").Select
ActiveCell.FormulaR1C1 = "CONTACT"
Range("BP1").Select
ActiveCell.FormulaR1C1 = "SPEED"
Range("BQ1").Select
ActiveCell.FormulaR1C1 = "DEFENSE"
Range("BR1").Select
ActiveCell.FormulaR1C1 = "SCOUT RANGE/LF"
Range("BS1").Select
ActiveCell.FormulaR1C1 = "LC"
Range("BT1").Select
ActiveCell.FormulaR1C1 = "C"
Range("BU1").Select
ActiveCell.FormulaR1C1 = "RC"
Range("BV1").Select
ActiveCell.FormulaR1C1 = "RF"
Range("BV2").Select
'*************************
' format cells
'*************************
Cells.Select
With Selection
.HorizontalAlignment = xlRight
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 2
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
'*************************
' MOVE NAMES COLUMN
'*************************
Columns("B:B").Select
Selection.Copy
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Range("C2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=HYPERLINK(RC[-1],RC[-2])"
Range("C2").Select
Selection.AutoFill Destination:=Range("C2:C87354")
Range("C2:C87354").Select
'*************************
' COLOR NAMES
'*************************
Columns("C:C").Select
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
Formula1:="=0"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=0"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
'*************************
' HIDE ROWS
'*************************
Columns("A:B").Select
Range("B1").Activate
Selection.EntireColumn.Hidden = True
'*************************
' color hearders
'*************************
Range("A1:M1").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 15773696
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Range("N1:AG1").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
With Selection.Font
.ColorIndex = xlAutomatic
.TintAndShade = 0
End With
Range("AH1:AR1").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 5287936
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Range("AS1:BJ1").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 12611584
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Range("BK1:BN1").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Range("BO1:BR1").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 49407
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Range("BS1:BW1").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorLight2
.TintAndShade = 0.399975585192419
.PatternTintAndShade = 0
End With
'*************************
' CHANGE NAME HEADER COLOR
'*************************'
Range("C1").Select
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=""NAME"""
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 15773696
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
'*************************
' FORMAT NUMBERS
'*************************
Columns("F:F").Select
Selection.NumberFormat = "0"
Columns("G:G").Select
Selection.NumberFormat = "0"
Columns("J:J").Select
Selection.NumberFormat = "0.000"
Columns("K:K").Select
Selection.NumberFormat = "0.000"
Columns("L:L").Select
Selection.NumberFormat = "0"
Columns("N:N").Select
Selection.NumberFormat = "0"
Columns("O:O").Select
Selection.NumberFormat = "0"
Columns("P:P").Select
Selection.NumberFormat = "0.000"
Columns("Q:Q").Select
Selection.NumberFormat = "0"
Columns("R:R").Select
Selection.NumberFormat = "0.000"
Columns("S:S").Select
Selection.NumberFormat = "0"
Columns("T:T").Select
Selection.NumberFormat = "0"
Columns("U:U").Select
Selection.NumberFormat = "0"
Columns("V:V").Select
Selection.NumberFormat = "0"
Columns("W:W").Select
Selection.NumberFormat = "0"
Columns("X:X").Select
Selection.NumberFormat = "0"
Columns("Y:Y").Select
Selection.NumberFormat = "0"
Columns("Z:Z").Select
Selection.NumberFormat = "0"
Columns("AA:AA").Select
Selection.NumberFormat = "0.000"
Columns("AB:AB").Select
Selection.NumberFormat = "0.000"
Columns("AC:AC").Select
Selection.NumberFormat = "0"
Columns("AD:AD").Select
Selection.NumberFormat = "0.00"
Columns("AE:AE").Select
Selection.NumberFormat = "0.000"
Columns("AF:AF").Select
Selection.NumberFormat = "0.00"
Columns("AG:AG").Select
Selection.NumberFormat = "0"
Columns("AH:AH").Select
Selection.NumberFormat = "0"
Columns("AJ:AJ").Select
Selection.NumberFormat = "0"
Columns("AK:AK").Select
Selection.NumberFormat = "0"
Columns("AL:AL").Select
Selection.NumberFormat = "0.000"
Columns("AM:AM").Select
Selection.NumberFormat = "0"
Columns("AN:AN").Select
Selection.NumberFormat = "0.000"
Columns("AO:AO").Select
Selection.NumberFormat = "0"
Columns("AP:AP").Select
Selection.NumberFormat = "0"
Columns("AQ:AQ").Select
Selection.NumberFormat = "0"
Columns("AR:AR").Select
Selection.NumberFormat = "0"
Columns("AS:AS").Select
Selection.NumberFormat = "0"
Columns("AT:AT").Select
Selection.NumberFormat = "0"
Columns("AU:AU").Select
Selection.NumberFormat = "0.000"
Columns("AV:AV").Select
Selection.NumberFormat = "0"
Columns("AW:AW").Select
Selection.NumberFormat = "0.000"
Columns("AX:AX").Select
Selection.NumberFormat = "0"
Columns("AY:AY").Select
Selection.NumberFormat = "0"
Columns("AZ:AZ").Select
Selection.NumberFormat = "0"
Columns("BA:BA").Select
Selection.NumberFormat = "0"
Columns("BB:BB").Select
Selection.NumberFormat = "0"
Columns("BC:BC").Select
Selection.NumberFormat = "0"
Columns("BD:BD").Select
Selection.NumberFormat = "0.000"
Columns("BE:BE").Select
Selection.NumberFormat = "0.000"
Columns("BF:BF").Select
Selection.NumberFormat = "0.000"
Columns("BG:BG").Select
Selection.NumberFormat = "0"
Columns("BH:BH").Select
Selection.NumberFormat = "0.00"
Columns("BI:BI").Select
Selection.NumberFormat = "0.000"
Columns("BJ:BJ").Select
Selection.NumberFormat = "0.00"
Columns("BK:BK").Select
Selection.NumberFormat = "0"
Columns("BL:BL").Select
Selection.NumberFormat = "0"
Columns("BM:BM").Select
Selection.NumberFormat = "0"
Columns("BN:BN").Select
Selection.NumberFormat = "0"
Columns("BO:BO").Select
Selection.NumberFormat = "0"
Columns("BP:BP").Select
Selection.NumberFormat = "0"
Columns("BQ:BQ").Select
Selection.NumberFormat = "0"
Columns("BR:BR").Select
Selection.NumberFormat = "0"
Columns("BS:BS").Select
Selection.NumberFormat = "0"
Columns("BT:BT").Select
Selection.NumberFormat = "0"
Columns("BU:BU").Select
Selection.NumberFormat = "0"
Columns("BV:BV").Select
Selection.NumberFormat = "0"
Columns("BW:BW").Select
Selection.NumberFormat = "0"
'***************************
' FREEZ FRAME
'***************************
Range("D2").Select
With ActiveWindow
.SplitColumn = 1
.SplitRow = 1
End With
ActiveWindow.FreezePanes = True
ActiveWindow.FreezePanes = False
ActiveWindow.FreezePanes = True
'*************************
' TEXXT TO COLUMN
'*************************
Columns("F:F").Select
Selection.TextToColumns Destination:=Range("F1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Columns("G:G").Select
Selection.TextToColumns Destination:=Range("G1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Columns("J:J").Select
Selection.TextToColumns Destination:=Range("J1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Columns("K:K").Select
Selection.TextToColumns Destination:=Range("K1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Columns("L:L").Select
Selection.TextToColumns Destination:=Range("L1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Columns("N:N").Select
Selection.TextToColumns Destination:=Range("N1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Columns("O:O").Select
Selection.TextToColumns Destination:=Range("O1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Columns("P:P").Select
Selection.TextToColumns Destination:=Range("P1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Columns("Q:Q").Select
Selection.TextToColumns Destination:=Range("Q1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Columns("R:R").Select
Selection.TextToColumns Destination:=Range("R1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Columns("S:S").Select
Selection.TextToColumns Destination:=Range("S1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Columns("T:T").Select
Selection.TextToColumns Destination:=Range("T1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Columns("U:U").Select
Selection.TextToColumns Destination:=Range("U1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Columns("V:V").Select
Selection.TextToColumns Destination:=Range("V1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Columns("W:W").Select
Selection.TextToColumns Destination:=Range("W1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Columns("X:X").Select
Selection.TextToColumns Destination:=Range("X1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Columns("Y:Y").Select
Selection.TextToColumns Destination:=Range("Y1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Columns("Z:Z").Select
Selection.TextToColumns Destination:=Range("Z1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Columns("AA:AA").Select
Selection.TextToColumns Destination:=Range("AA1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Columns("AB:AB").Select
Selection.TextToColumns Destination:=Range("AB1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Columns("AC:AC").Select
Selection.TextToColumns Destination:=Range("AC1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Columns("AD:AD").Select
Selection.TextToColumns Destination:=Range("AD1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Columns("AE:AE").Select
Selection.TextToColumns Destination:=Range("AE1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Columns("AF:AF").Select
Selection.TextToColumns Destination:=Range("AF1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Columns("AG:AG").Select
Selection.TextToColumns Destination:=Range("AG1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Columns("AH:AH").Select
Selection.TextToColumns Destination:=Range("AH1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Columns("AJ:AJ").Select
Selection.TextToColumns Destination:=Range("AJ1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Columns("AK:AK").Select
Selection.TextToColumns Destination:=Range("AK1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Columns("AL:AL").Select
Selection.TextToColumns Destination:=Range("AL1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Columns("AM:AM").Select
Selection.TextToColumns Destination:=Range("AM1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Columns("AN:AN").Select
Selection.TextToColumns Destination:=Range("AN1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Columns("AO:AO").Select
Selection.TextToColumns Destination:=Range("AO1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Columns("AP:AP").Select
Selection.TextToColumns Destination:=Range("AP1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Columns("AQ:AQ").Select
Selection.TextToColumns Destination:=Range("AQ1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Columns("AR:AR").Select
Selection.TextToColumns Destination:=Range("AR1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Columns("AS:AS").Select
Selection.TextToColumns Destination:=Range("AS1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Columns("AT:AT").Select
Selection.TextToColumns Destination:=Range("AT1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Columns("AU:AU").Select
Selection.TextToColumns Destination:=Range("AU1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Columns("AV:AV").Select
Selection.TextToColumns Destination:=Range("AV1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Columns("AW:AW").Select
Selection.TextToColumns Destination:=Range("AW1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Columns("AX:AX").Select
Selection.TextToColumns Destination:=Range("AX1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Columns("AY:AY").Select
Selection.TextToColumns Destination:=Range("AY1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Columns("AZ:AZ").Select
Selection.TextToColumns Destination:=Range("AZ1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Columns("BA:BA").Select
Selection.TextToColumns Destination:=Range("BA1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Columns("BB:BB").Select
Selection.TextToColumns Destination:=Range("BB1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Columns("BC:BC").Select
Selection.TextToColumns Destination:=Range("BC1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Columns("BD:BD").Select
Selection.TextToColumns Destination:=Range("BD1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Columns("BE:BE").Select
Selection.TextToColumns Destination:=Range("BE1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Columns("BE:BE").Select
Selection.TextToColumns Destination:=Range("BE1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Columns("BF:BF").Select
Selection.TextToColumns Destination:=Range("BF1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Columns("BG:BG").Select
Selection.TextToColumns Destination:=Range("BG1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Columns("BH:BH").Select
Selection.TextToColumns Destination:=Range("BH1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Columns("BI:BI").Select
Selection.TextToColumns Destination:=Range("BI1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Columns("BJ:BJ").Select
Selection.TextToColumns Destination:=Range("BJ1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Columns("BK:BK").Select
Selection.TextToColumns Destination:=Range("BK1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Columns("BL:BL").Select
Selection.TextToColumns Destination:=Range("BL1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Columns("BM:BM").Select
Selection.TextToColumns Destination:=Range("BM1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Columns("BN:BN").Select
Selection.TextToColumns Destination:=Range("BN1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Columns("BO:BO").Select
Selection.TextToColumns Destination:=Range("BO1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Columns("BP:BP").Select
Selection.TextToColumns Destination:=Range("BP1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Columns("BQ:BQ").Select
Selection.TextToColumns Destination:=Range("BQ1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Columns("BR:BR").Select
Selection.TextToColumns Destination:=Range("BR1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Columns("BS:BS").Select
Selection.TextToColumns Destination:=Range("BS1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Columns("BT:BT").Select
Selection.TextToColumns Destination:=Range("BT1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Columns("BU:BU").Select
Selection.TextToColumns Destination:=Range("BU1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Columns("BV:BV").Select
Selection.TextToColumns Destination:=Range("BV1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Columns("BW:BW").Select
Selection.TextToColumns Destination:=Range("BW1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
'*************************
' auto filter
'*************************
Selection.AutoFilter
'*************************
' spacing
'*************************
' Selection.ColumnWidth = 32.82
Selection.ColumnWidth = 50.36
Cells.EntireColumn.AutoFit
'*************************
' FORMATTING SEARCH
'*************************
Columns("BK:BN").Select
Range("BN1").Activate
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
Formula1:="=89"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Borders(xlLeft)
.LineStyle = xlContinuous
.ThemeColor = 1
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.FormatConditions(1).Borders(xlRight)
.LineStyle = xlContinuous
.ThemeColor = 1
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.FormatConditions(1).Borders(xlTop)
.LineStyle = xlContinuous
.ThemeColor = 1
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.FormatConditions(1).Borders(xlBottom)
.LineStyle = xlContinuous
.ThemeColor = 1
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Columns("BO:BR").Select
Range("BR1").Activate
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
Formula1:="=89"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.ColorIndex = xlAutomatic
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Borders(xlLeft)
.LineStyle = xlContinuous
.ThemeColor = 2
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.FormatConditions(1).Borders(xlRight)
.LineStyle = xlContinuous
.ThemeColor = 2
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.FormatConditions(1).Borders(xlTop)
.LineStyle = xlContinuous
.ThemeColor = 2
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.FormatConditions(1).Borders(xlBottom)
.LineStyle = xlContinuous
.ThemeColor = 2
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 49407
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Columns("BS:BW").Select
Range("BW1").Activate
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=5"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.ColorIndex = xlAutomatic
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Borders(xlLeft)
.LineStyle = xlContinuous
.ThemeColor = 1
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.FormatConditions(1).Borders(xlRight)
.LineStyle = xlContinuous
.ThemeColor = 1
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.FormatConditions(1).Borders(xlTop)
.LineStyle = xlContinuous
.ThemeColor = 1
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.FormatConditions(1).Borders(xlBottom)
.LineStyle = xlContinuous
.ThemeColor = 1
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 15773696
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
End Sub
Last edited by a moderator: