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:
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