Thread: [SOLVED] Pasting Only Background Colors Across Rows

So, I've been trying to figure this one out for a while...

I have a column of data that has several different background colors assigned to each cell. For example, A2 is blue, A3 is orange, A4 through A10 is yellow, and then A11 is blue again.

I would like to copy and paste these background colors across all the rows in the spreadsheet; however, if I simply copy and paste special > formats, it also pastes the column width and other formatting options from column A that I do not wish to have changed on my spreadsheet.

I have done some research on this and I now know that each color is assigned a number and I can have the number returned using a function or a macro (I'm not really certain of the difference or if there even is one) and then use that value to then set the background color of each row accordingly.

The problem is that even after reading articles like this and this, I still can't seem to figure out how to put the information together to do what I want to do.

Can anyone interpret this for me and possibly produce a code that does what I am needing?

Thanks in advance!

-Amy Kate

This code will transfer the colour of the selected cells to the whole row.

Code:

Sub copy_colours_across_rows()
Dim rCell As Range
For Each rCell In Selection
rCell.EntireRow.Interior.ColorIndex = rCell.Interior.ColorIndex
Next
End Sub

To input the code, use ALT & F11 to start the VBA editor

Go to Insert, Module

Paste the code

Close the VB editor

In excel, select the cells from which you want to transfer the colour.

Go to Macros, run macro, and select copy_colours_across_rows

Change this:

Code:

rCell.EntireRow.Interior.ColorIndex = rCell.Interior.ColorIndex

For this:

Code:

rCell.EntireRow.Font.ColorIndex = rCell.Font.ColorIndex

If you want to find out how to adjust formatting with VBA, record a macro in which you alter the formatting, then take a look at it in the VBA editor.

Presuming your data starts at A1, and has no gaps

Code:

Sub copy_colours_across_rows()

Dim rCell As Range, rRange as Range

set rRange = Range(Range("A1"), Range("A1").End(xlDown))

For Each rCell In rRange
rCell.EntireRow.Interior.ColorIndex = rCell.Interior.ColorIndex
Next

End Sub

Presuming your data starts at A1, and has no gaps

Slight modification to Sweep's code. If you ever have blank cells between the first and last row, selection of the range will omit rows below the empty cell and thus cause some rows to not get the color change.

This code mod will select the range to the last used row in column-A and will not be affected by blank cells in between.

Code:

Option Explicit

Sub copy_colours_across_rows()

    Dim rCell As Range, rRange As Range, lrow As Long
    
    lrow = Cells(Rows.Count, 1).End(xlUp).Row
    
    Set rRange = Range(Cells(1, 1), Cells(lrow, 1))
    
    Application.ScreenUpdating = False
    
    On Error Resume Next
    For Each rCell In rRange
        rCell.EntireRow.Interior.ColorIndex = rCell.Interior.ColorIndex
    Next
    
    Application.ScreenUpdating = True
    
End Sub

Can someone mark this solved for me, since it is an old post?

Just go to the top of YOUR first post, select Thread Tools, Go Advanced, then choose the [SOLVED] prefix.