Menu

Excel - Assign Macro to all yellow highlighted cells

April 2018




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

Published by aakai1056. Latest update on October 26, 2012 at 04:13 AM by Jeff.
This document, titled "Excel - Assign Macro to all yellow highlighted cells," is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (https://ccm.net/).
Excel - VBA - Creating a reminder
Excel - Compare two columns and delete duplicates