CODEWITHSUNDEEP
vbaexcel
babou
babou

Reputation: 237

If my cells have no color, then copy it

Good morning,

I have a range in C8:C17 some of cells are in red, and some without color. I want that cells without colors are transferred into column A.

This is my code :

Dim a As Long
    a = 1 'we set the row where we start filling in the single sames
     If Range("C8:C17").Interior.ColorIndex = xlColorIndexNone Then
        Cells(a, "A").Value = Range("C8:C17").Value
        a = a + 1
     End If

Upvotes: 0

Views: 1985

Answers (1)

Shai Rado
Shai Rado

Reputation: 33692

The code below loops through all the cells in Range("C8:C17"), and checks if the current cell is not colored. If it's not colores, then it pastes it to column A at the next empty row (starting from the first row).

Option Explicit

Sub CopyColCells()

Dim a As Long
Dim C As Range

a = 1 'we set the row where we start filling in the single sames
For Each C In Range("C8:C17")
    If C.Interior.ColorIndex = xlColorIndexNone Then
       Cells(a, "A").Value = C.Value
       a = a + 1
    End If
Next C

End Sub

Upvotes: 4

Related Questions