Hi, I inserted a formula into a macro to start in cell H2. It worked in my test sheet, but in the working sheet nothing happens.
Please tell me what I'm missing!
The formula to go into H2 and copy down is -
=IF(T2<>110,"Incorrect amount billed; to be investigated. Comp fee "&TEXT(Q2,"MMM-YY"),IF(C2="OK","OK to Bill - Comp fee "&TEXT(Q2,"MMM-YY"),("Comp fee - "&TEXT(Q2,"MMM-YY"))))
It is towards the end of the code. Once that formula is run then if the cell does not return "OK to Bill" it will be formatted to yellow. Everything else works fine.
The full code is -
Please tell me what I'm missing!
The formula to go into H2 and copy down is -
=IF(T2<>110,"Incorrect amount billed; to be investigated. Comp fee "&TEXT(Q2,"MMM-YY"),IF(C2="OK","OK to Bill - Comp fee "&TEXT(Q2,"MMM-YY"),("Comp fee - "&TEXT(Q2,"MMM-YY"))))
It is towards the end of the code. Once that formula is run then if the cell does not return "OK to Bill" it will be formatted to yellow. Everything else works fine.
The full code is -
Rich (BB code):
Sub Format_Sabic_RevenueRec()
'
' Format_Sabic_RevenueRec Macro
'
' Keyboard Shortcut: Ctrl+m
'
Columns("A:A").Select
Selection.Delete Shift:=xlToLeft
Columns("C:C").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("F:H").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("C:C").EntireColumn.AutoFit
Columns("B:B").EntireColumn.AutoFit
Range("C2").Select
ActiveCell.FormulaR1C1 = "=if(OR(d3=d2,d2=d3,d2=d1,d1=d2),""DUP"",""OK"")"
Selection.NumberFormat = "General"
ActiveCell.FormulaR1C1 = _
"=IF(OR(R[1]C[1]=RC[1],RC[1]=R[1]C[1],RC[1]=R[-1]C[1],R[-1]C[1]=RC[1]),""DUP"",""OK"")"
Selection.AutoFill Destination:=Range("C2:C86")
Range("C2:C86").Select
Range("F1").Select
ActiveCell.FormulaR1C1 = "vStart Date"
Range("G1").Select
ActiveCell.FormulaR1C1 = "vEnd Date"
Range("H1").Select
ActiveCell.FormulaR1C1 = "Comments"
Range("F2:H2").Select
Range("H2").Activate
Selection.NumberFormat = "General"
Range("F2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],Tracker!C[-4]:C[1],6,FALSE)"
Selection.AutoFill Destination:=Range("F2:F86")
Range("F2:F86").Select
Range("G2").Select
ActiveCell.FormulaR1C1 = _
"=IF(VLOOKUP(RC[-3],Tracker!C[-5]:C[2],8,FALSE)="""",VLOOKUP(Fees!RC[-3],Tracker!C[-5]:C[1],7,FALSE),VLOOKUP(Fees!RC[-3],Tracker!C[-5]:C[2],8,FALSE))"
Selection.AutoFill Destination:=Range("G2:G86")
Range("G2:G86").Select
Range("D2").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWindow.ScrollRow = 55
ActiveWindow.ScrollRow = 54
ActiveWindow.ScrollRow = 51
ActiveWindow.ScrollRow = 48
ActiveWindow.ScrollRow = 44
ActiveWindow.ScrollRow = 40
ActiveWindow.ScrollRow = 37
ActiveWindow.ScrollRow = 33
ActiveWindow.ScrollRow = 22
ActiveWindow.ScrollRow = 15
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 1
Rows("2:2").Select
Range("B2").Activate
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$C2=""DUP"""
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 15773696
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.Copy
Rows("3:3").Select
Range("B3").Activate
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Selection.Copy
Rows("4:350").Select
Range("B4").Activate
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("A1").Select
Columns("A:A").EntireColumn.AutoFit
Columns("F:G").Select
Application.CutCopyMode = False
Selection.NumberFormat = "m/d/yyyy"
Range("D:D").Select
With Selection
Selection.NumberFormat = "General"
.Value = .Value
ActiveCell.FormulaH2 = _
"=IF(T2<>110,""Incorrect amount billed; to be investigated. Comp fee ""&TEXT(Q2,""MMM-YY""),IF(C2=""OK"",""OK to Bill - Comp fee ""&TEXT(Q2,""MMM-YY""),(""Comp fee - ""&TEXT(Q2,""MMM-YY""))))"
Range("H2").Select
Selection.AutoFill Destination:=Range("H2:H87")
Range("H2:H87").Select
Columns("H:H").Select
Selection.FormatConditions.Add Type:=xlTextString, String:="OK to Bill", _
TextOperator:=xlDoesNotContain
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Range("I1").Select
Selection.Copy
Range("H1").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
End With
End Sub
Last edited by a moderator: