Assign Macro to all yellow highlighted cells

Solved/Closed
Alby - Feb 4, 2010 at 08:17 PM
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Mar 14, 2010 at 09:08 AM
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

rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Feb 5, 2010 at 05:18 AM
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 ?
0
Albylo Posts 1 Registration date Thursday February 4, 2010 Status Member Last seen February 6, 2010
Feb 6, 2010 at 05:24 AM
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
0
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Mar 14, 2010 at 09:08 AM
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
0