Sub Copy_Formulas_Only() Dim row As Single row = ActiveCell.row Selection.EntireRow.Insert Rows(row - 1).Copy Rows(row).Select On Error Resume Next Selection.PasteSpecial Paste:=xlPasteFormulas Selection.SpecialCells(xlCellTypeConstants).ClearContents On Error GoTo 0 Application.CutCopyMode = False End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Application.EnableEvents = False 'If Target.Address = "ALL THE YELLOW CELL IN THE WORKSHEET" Then For Each CELL In Target If (CELL.Interior.Color = 65535) Then Dim row As Single row = ActiveCell.row Selection.EntireRow.Insert Rows(row - 1).Copy Rows(row).Select On Error Resume Next Selection.PasteSpecial Paste:=xlPasteFormulas Selection.SpecialCells(xlCellTypeConstants).ClearContents Application.EnableEvents = True On Error GoTo 0 End If Next Application.CutCopyMode = False End Sub
DON'T MISS