Excel - Assign Macro to all yellow highlighted cells

December 2016




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

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

Note


Thanks to rizvisa1 for this tip on the forum.

Related :

This document entitled « Excel - Assign Macro to all yellow highlighted cells » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.