Excel VBA loop through cells to find Conditional Formatted cells and copy paste on other sheet

Question

My issue:

I want to loop through a range, and whenever it finds a conditional formatted cell, it should copy the cell to the left to the cell to the right of it. And then afterwards paste it into an other worksheet.

My code loops through the cells easily, but doesn't copy the information, and paste it into the other sheet.

My Code:

        Sub LoopForCondFormatCells()

        Dim sht3, sht4 As Worksheet
        Dim ColB, c As Range
        Set sht3 = Sheets("Compare")
        Set sht4 = Sheets("Print ready")

        ColB1 = Range("G3:G86")
        Set ColB = Range(ColB1)

        For Each c In ColB.Cells
        If c.Interior.Color = RGB(250, 191, 143) Then _
    'Must be here, where the code is wrong. I actually don't need to look for .Count _
    Since I need the code to find a cell, and if it is conditionally formatted _
    , then do following steps.
                CValue = c.Address(False, False, xlA1)
                CValueOffsetL = Range(CValue).Offset(0, -1).Address(False, False, xlA1)
                CValueOffsetR = Range(CValue).Offset(0, 1).Address(False, False, xlA1)
                sht3.Range(CValueOffsetL, CValueOffsetR).Copy
                KvikOffIns = sht4.Range(HosKvikOff).Offset(0, -1).Address(False, False, xlA1)
                sht4.Activate
                sht4.Range(KvikOffIns).PasteSpecial xlPasteFormats
                End If
            Next c

        End Sub

Goal:

I want the macro to loop through the cells, and find whatever cells, which has the color "RGB(250, 191, 143)". Whenever it comes across a cell, which is colored with "RGB(250,191,143)", it should do the steps, or at least a cell that is colored:

            CValue = c.Address(False, False, xlA1)
            CValueOffsetL = Range(CValue).Offset(0, -1).Address(False, False, xlA1)
            CValueOffsetR = Range(CValue).Offset(0, 1).Address(False, False, xlA1)
            sht3.Range(CValueOffsetL, CValueOffsetR).Copy
            KvikOffIns = sht4.Range(HosKvikOff).Offset(0, -1).Address(False, False, xlA1)
            sht4.Activate
            sht4.Range(KvikOffIns).PasteSpecial xlPasteFormats

What should I write in my "If c Is" line to get the macro to do what I want it to do?

Instead of formatting cells that meet a certain condition, and then copying cells that are formatted, why not just copy cells that meet the condition? (ie., instead of A→B→C, why not A→C?) Plus this can probably be accomplished without VBA. — ashleedawg

StaP StaP · Accepted Answer · 2018-01-05T14:40:49

I am not sure I understand the need to do a range of range but why don't you use directly :

If c.FormatConditions.Count = 1 Then

Excel VBA loop through cells to find Conditional Formatted cells and copy paste on other sheet

1 Answers