(Solved) – Copy colored cells in another spreadsheet to current spreadsheet


I have searched some similar posts here and the Web,
I feel my code should be running, unfortunately,
I got a compile error: invalid outside procedure.

What I want to do is to copy the same range(“F2: F403”) in the worksheetB in the workbookB
to the same range(“F2:F403”) in worksheet A in ThisWorkBook if the cells in worksheetB
is colored in green(colorIndex=4).
Otherwise, no doing copying.

I am pretty new in VBA(not used for one year).
Any help would be highly appreciated!

    Option Explicit

    Sub fillgreen()

    Dim wb As Workbook: Set wb = ThisWorkbook
    Dim ws, ws1, ws2 As Worksheet
    Dim SUD_F As Range, S_Cell As Range, U_Cell As Range, Usage_F As Range
    Dim i As Variant

    Set ws1 = ThisWorkbook.Worksheets("WorksheetA")
    Set ws2 = Workbooks("WorkbookB.xlsx").Worksheets("WorksheetB")

    With ws1
    Set SUD_F = ThisWorkbook.Worksheets("WorksheetA").Range("F2: F403")
    End With

    With ws2
    Set Usage_F = Workbooks("WorkbookB.xlsx").Worksheets("WorksheetB").Range("F2: F403")
    End With

    ' now fill the Usage_F green value to SUD_F

    For i = 2 To 403
    If Usage_F.Cells(i, "F").Value.Interior.ColorIndex = 4 Then
    SUD_F.Cells(i, "F").Value = Usage_F.Cells(i, "F").Value
    SUD_F.Cells(i, "F").Value = SUD_F.Cells(i, "F").Value

    End If
    Next i

    End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *