Issue

Hello, I am in currently in a pickle ... I hope all the masters could lend a hand to this VBA noob...

Right now I wrote a macro to copy all formula without retaining the value on the active cells and I would like to apply this macro to all yellow highlighted yellow cells in the spreadsheet .... How to do that ???

my current code is just

(I still can't think what to put to make only yellow highlighted cells to contain this macro)

Sub Copy_Formulas_Only() Dim row As Single row = ActiveCellow 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

Solution

It will be triggered, as you select range of cell. But I guess you know better what you need to do.

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 = ActiveCellow 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

Note

Thanks to rizvisa1 for this tip on the forum.