How to copy highlighted events from a calendar with dates to other excel sheet and auto update if events are preponed or postponed along with dates?

Viewed 25

I have a calendar format which have different events highlighted with color i want to copy only highlighted cells along with there dates to other sheet and if any event is preponed or postponed I want it get automatically updated in other sheet.

in the image only highlited events are to be copied: screenshot

Used the below VBA code to copy given rgb colored cell to other worksheet but I have to run this code again and again and values are continued below only everytime I run I want it to get updated automatically if new green event is made or preponed or postponed and it shouldnt repeat and copy.

Please help guys.

Sub CopyHighlightedTransactions()

Dim TransIDField As Range
Dim TransIDCell As Range
Dim ATransWS As Worksheet
Dim HTransWS As Worksheet

Set ATransWS = Worksheets("Factory Plan")
Set TransIDField = ATransWS.Range("H253", ATransWS.Range("H253").End(xlDown))
Set HTransWS = Worksheets("FR")


For Each TransIDCell In TransIDField

    If TransIDCell.Interior.Color = RGB(84, 246, 134) Then
        
        TransIDCell.Resize(1, 2).Copy Destination:= _
            HTransWS.Range("B1").Offset(HTransWS.Rows.Count - 1, 0).End(xlUp).Offset(1, 0)
            
    End If

Next TransIDCell

HTransWS.Columns.AutoFit

End Sub
0 Answers
Related