CODEWITHSUNDEEP
vba
Chen
Chen

Reputation: 383

Copy colored cells in another spreadsheet to current spreadsheet

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! Thanks! 

    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
        Else
            SUD_F.Cells(i, "F").Value = SUD_F.Cells(i, "F").Value

    End If
    Next i


    End Sub

Upvotes: 0

Views: 49

Answers (2)

Chen
Chen

Reputation: 383

Many thanks to @Tim continuous help, and I know it is not easy to see the problem off hand. After searching for some other tutorial outside, I succeeded with the code below:

Option Explicit

Sub Color()

Dim Workbook As ThisWorkbook
Dim workksheet As Worksheet
Dim i As Variant
Dim S_range, U_range As Range
Dim rw As Range

Set S_range = ThisWorkbook.Worksheets("Journals with no use").Range("F2:F403")
Set U_range = ThisWorkbook.Worksheets("Use").Range("F2: F403")

For i = 2 To 403
    If U_range.Cells.Rows(i).Interior.Color = RGB(198, 224, 180) Then
        S_range.Cells.Rows(i).Value = U_range.Cells.Rows(i).Value
    End If
Next i

End Sub

Upvotes: 0

Tim Williams
Tim Williams

Reputation: 166540

Maybe more like this:

Sub fillgreen()

    Dim SUD_F As Range, Usage_F As Range
    Dim i As Long, v

    Set SUD_F = ThisWorkbook.Worksheets("WorksheetA").Range("F2:F403")
    Set Usage_F = Workbooks("WorkbookB.xlsx").Worksheets("WorksheetB").Range("F2:F403")

    For i = 1 To Usage_F.Cells.Count

        If Usage_F.Cells(i, "F").Interior.ColorIndex = 4 Then
            SUD_F.Cells(i, "F").Value = Usage_F.Cells(i, "F").Value
        Else
            SUD_F.Cells(i, "F").Value = SUD_F.Cells(i, "F").Value '??
        End If

    Next i

End Sub

Upvotes: 1

Related Questions