Copy a cell with its color - Excel

Question

I have a data sheet as below

COLA        COLB
ABC          10
             10
             15
XYZ          10
             15
             15

I copied it from another pivot where Col A groups to more than one value in Col B. Now I have used the below step to fill all the blank cells in Col A with value from the above cell,

Select Col A Go to Special -> Select blanks -> OK -> Enter formula in cell A3 as "=A2" -> ctrl + Enter.

This has copied all the values from the cell above into all the blank cells in Col A.

But I want the blank cell to copy the color also from the parent cell if they are highlighted.

How can I do this in excel?

Cheers!!

Karthick Gunasekaran Karthick Gunasekaran · Accepted Answer · 2016-04-18T08:21:28

try the code below

Sub test()
    lastrow = Range("B" & Rows.Count).End(xlUp).Row
    For i = 2 To lastrow
        If Cells(i, 1) = "" Then
            If Cells(i - 1, 1).Interior.ColorIndex > 0 Then
                Cells(i, 1) = Cells(i - 1, 1)
                Cells(i, 1).Interior.Color = Cells(i - 1, 1).Interior.Color
            Else
                Cells(i, 1) = Cells(i - 1, 1)
            End If
        End If
    Next i
End Sub

Copy a cell with its color - Excel

1 Answers