Assign Macro to all yellow highlighted cells

Solved/Closed
Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
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 cant 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

Thanks

2 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
are you saying that even though you want to copy the row but in the row you want only to copy those cells that were colored yellow ?
Posts
1
Registration date
Thursday February 4, 2010
Status
Member
Last seen
February 6, 2010

NO i want to assign the code above to "ALL YELLOW HIGHLIGHTED CELL IN THE WORKSHEET"
i know roughly that i need to start with something like this .....

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "ALL THE YELLOW CELL IN THE WORKSHEET" 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
On Error GoTo 0
Application.CutCopyMode = False
End Sub

I am also aware that this will slow down my worksheet abit .. and i just want to see how much slower it going make my work....
all help will be great appreciated ... thanks for replying risvisa1
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Though I realize that it has been since some time you were looking for an approach. But still hopefully it will be of some use. I dont know if SelectionChange is the best method to be used. 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