Sub FormatReviewClaimsWorksheet()
'
' FormatReviewClaimsWorksheet Macro
'
'
Rows("1:1").Select
Selection.Font.Bold = True
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Rows("1:1").Select
ActiveWindow.FreezePanes = True
Call AddColumnsWithHeadersResize
Range("A1").EntireRow.Insert
Call FindEx1
Selection.NumberFormat = "$#,##0.00"
ActiveCell.FormulaR1C1 = "=SUM(R[2]C:R[3999]C)"
Call FindEx2
Selection.NumberFormat = "$#,##0.00"
ActiveCell.FormulaR1C1 = "=SUM(R[2]C:R[3999]C)"
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=COUNTA($A1:$AL4000)>0"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Borders(xlLeft)
.LineStyle = xlContinuous
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.FormatConditions(1).Borders(xlRight)
.LineStyle = xlContinuous
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.FormatConditions(1).Borders(xlTop)
.LineStyle = xlContinuous
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.FormatConditions(1).Borders(xlBottom)
.LineStyle = xlContinuous
.TintAndShade = 0
.Weight = xlThin
End With
Selection.FormatConditions(1).StopIfTrue = False
Rows("3:4000").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlTop
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlTop
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
End Sub
Sub MoveData()
Sheets("Exp Trend_Repair Grp").Select
Rows("2:2").Select
Selection.Insert Shift:=xlDown
Range("G2").Select
ActiveCell.FormulaR1C1 = "=SUBSTITUTE(R[1]C,""USD"","""")+0"
Range("G2").Select
Selection.AutoFill Destination:=Range("G2:K2"), Type:=xlFillDefault
Range("G2:K2").Select
Selection.Copy
Sheets("Data").Select
Range("E4:I4").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Sheets("Review Summary").Select
Range("E12:F12").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=Data!R[-8]C"
Range("E13:F13").Select
ActiveCell.FormulaR1C1 = "=Data!R[-9]C[1]"
Range("E14:F14").Select
ActiveCell.FormulaR1C1 = "=Data!R[-10]C[2]"
Range("E15:F15").Select
ActiveCell.FormulaR1C1 = "=Data!R[-11]C[3]"
Range("E16:F16").Select
ActiveCell.FormulaR1C1 = "=Data!R[-12]C[4]"
Range("E17:F17").Select
Sheets("Exp Trend_Repair Grp").Select
Rows("2:2").Select
Selection.Delete Shift:=xlUp
Sheets("Review Summary").Select
Range("F3:K3").Select
ActiveCell.FormulaR1C1 = "='Svc Agent Criteria'!R[-1]C[-3]"
Range("F4:J4").Select
ActiveCell.FormulaR1C1 = "='Svc Agent Criteria'!R[-2]C[7]"
Range("K4").Select
ActiveCell.FormulaR1C1 = "='Svc Agent Criteria'!R[-2]C[3]"
Range("F5:G5").Select
ActiveCell.FormulaR1C1 = "='Svc Agent Criteria'!R[-3]C[-5]"
Range("F7:K7").Select
Sheets("Svc Agent Criteria").Select
Range("V2").Select
Sheets("Review Summary").Select
Range("F7:K7").Select
ActiveCell.FormulaR1C1 = "='Svc Agent Criteria'!R[-5]C[16]"
Range("E18:F18").Select
Sheets("Svc Agent Criteria").Select
Range("G2").Select
Sheets("Review Summary").Select
ActiveCell.FormulaR1C1 = "='Svc Agent Criteria'!R[-16]C[2]"
Range("E29:F29").Select
ActiveCell.FormulaR1C1 = "='Svc Agent Criteria'!R[-27]C[15]"
Range("E30:F30").Select
ActiveCell.FormulaR1C1 = "='Svc Agent Criteria'!R[-28]C[18]"
Range("E36:F36").Select
ActiveCell.FormulaR1C1 = "='Svc Agent Criteria'!R[-34]C[3]"
Range("E31:F31").Select
ActiveCell.FormulaR1C1 = "='Exp Trend_Repair Grp'!R[-29]C[14]"
Range("E32:F32").Select
ActiveCell.FormulaR1C1 = "='Exp Trend_Repair Grp'!R[-30]C[12]"
Range("E33").Select
End Sub
Sub FindEx1()
Cells.Find(What:="Potential Debit Amt", After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Offset(-1, 0).Activate
End Sub
Sub FindEx2()
Cells.Find(What:="Debit Amt", After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Offset(-1, 0).Activate
End Sub