Macro That Inserts a New Row and Defines Formulas to Cells

[mmore073]

I have created this Macro that inserts a new raw and preserves all of the conditional formatting, however, when I try to define formulas to specific cells it stops working. Please let me know where I'm going wrong.


Private Sub CommandButton1_Click()
Sheets("Main").Range("A7").Select
ActiveCell.EntireRow.Insert Shift:=xlDown
Sheets("Main").Range("A7").Select
ActiveCell.EntireRow.Interior.ColorIndex = 0
Sheets("Main").Range("A7").Select
ActiveCell.EntireRow.Font.Bold = False
Sheets("Main").Range("A7:B7,D7:G7").HorizontalAlignment = xlLeft
Sheets("Main").Range("H7").HorizontalAlignment = xlRight
Sheets("Main").Range("A8:AW8").Select
Selection.Copy
Sheets("Main").Range("A7:AW7").Select
Selection.PasteSpecial Paste:=xlPasteAllMergingConditionalFormats
Application.CutCopyMode = False
Sheets("Main").Range("A7:AW7").ClearContents
Sheets("Main").Range("p7").Select
ActiveCell.Formula = "=IFS(AND($Q7<>"",$R7<>""),"Proposal Sent",AND(Q7="",R7<>""),"Error",AND($Q7="",$R7=""),"", _
AND($Q7>=TODAY()),SUM(TODAY()-$Q7),AND($Q7<TODAY()),SUM(TODAY()-$Q7))"
Sheets("Main").Range("b7").Select
ActiveCell.Formula = "=p7"
End Sub

Thanks.

[arnelgp]

you "double" all your Quotes:


ActiveCell.Formula = "=IFS(AND($Q7<>"""",$R7<>""""),""Proposal Sent"", _
AND(Q7="""",R7<>""""),""Error"",AND($Q7="""",$R7=""""),"""", _
AND($Q7>=TODAY()),SUM(TODAY()-$Q7), _
AND($Q7<TODAY()),SUM(TODAY()-$Q7))"

[Bob Phillips]

And do get rid of that horrible selecting


Private Sub CommandButton1_Click()

With Sheets("Main")

With .Range("A7")

.EntireRow.Insert Shift:=xlDown
.EntireRow.Interior.ColorIndex = 0
.EntireRow.Font.Bold = False
End With
.Range("A7:B7,D7:G7").HorizontalAlignment = xlLeft
.Range("H7").HorizontalAlignment = xlRight
.Range("A8:AW8").Copy
.Range("A7:AW7").PasteSpecial Paste:=xlPasteAllMergingConditionalFormats
.Range("A7:AW7").ClearContents ""
.Range("P7").Formula = "=IFS(AND($Q7<>"",$R7<>""""),""Proposal Sent""," & _
"AND(Q7="""",R7<>""""),""Error""," & _
"AND($Q7="""",$R7=""""),""""," & _
"AND($Q7>=TODAY()),SUM(TODAY()-$Q7)," & _
"AND($Q7<TODAY()),SUM(TODAY()-$Q7))"
.Range("B7").Formula = "=P7"
End With
End Sub