Copy Cells to another sheet based on fill colour

[pwilson06]

HI,
I have a page set up that has a macro to fill the cell yellow(Interior.ColorIndex = 6) when the value changes. I use this page to do price changes for products that we sell, which is a catalouge of over 600 products.
Is there a way (VBA??) I can use to have the highlighted cells copied to another sheet to show a customer just the items/prices that have changed rather than sending them a product list with all 600+ products.
Some of the cells that need to be copied have formulas in them but I only need to paste the values.
Thanks

 

[Trebor76]

Try this:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim lngPasteRow As Long
    
    If Target.Column >= 3 And Target.Column <= 16 And Target.Row >= 4 Then 'Only works on range C4:P [whatever row]
        With Application
            .ScreenUpdating = False
            .EnableEvents = False
        End With
        Target.Interior.ColorIndex = 6
        If Target.Column = 15 Then
            On Error Resume Next
                lngPasteRow = Sheets("Rayners Price Changes").Range("A:C").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
                If lngPasteRow = 0 Then
                    lngPasteRow = 3
                Else
                    lngPasteRow = lngPasteRow + 1
                End If
            On Error Resume Next
            Sheets("Rayners Price Changes").Range("A" & lngPasteRow).Value = Range("C" & Target.Row).Value
            Sheets("Rayners Price Changes").Range("B" & lngPasteRow).Value = Range("D" & Target.Row).Value
            Sheets("Rayners Price Changes").Range("C" & lngPasteRow).Value = Range("O" & Target.Row).Value
        End If
        With Application
            .EnableEvents = True
            .ScreenUpdating = True
        End With
    End If
    
End Sub