0
Thanks

A few words of thanks would be greatly appreciated.

Excel - Assign Macro to all yellow highlighted cells

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.

0
Thanks

A few words of thanks would be greatly appreciated.

Ask a question
CCM is a leading international tech website. Our content is written in collaboration with IT experts, under the direction of Jean-François Pillou, founder of CCM.net. CCM reaches more than 50 million unique visitors per month and is available in 11 languages.
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 (ccm.net).

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!