excel vba copy cell background color

This is an overview.

How to Copy Cell Background Color in Excel VBA (Excel VBA Copy Cell Background Color)

How to Launch the VBA Editor in Excel

Press Alt + F11 to open Microsoft Visual Basic.

Select Insert > Module to open a blank module.

Enter your code.

Example 1 – Copy the Cell Background Color Using the Interior.Color Property

The Interior.Color Property method gets the color value of the source cell interior and applies it to the target cell.

Use the code:

Sub CopyCellBackgroundColor()
    'Set the source cell
    Set sourceCell = Range("B4:E4")
    'Set the destination cell
    Set destinationCell = Range("B14:E14")
    'Copy the background color from the source cell to the destination cell
    destinationCell.Interior.Color = sourceCell.Interior.Color
End Sub

The background color in B4:E4 is copied and pasted into B14:E14.

It sets the sourceCell variable to B4:E4 and the destinationCell variable to B14:E14. The Interior.Color property copies the background color from the source cell and pastes it into the destination cell.

This is the output.

Example 2 – Copy the Cell Background Color using the xlPasteFormats Property

Use the following VBA code:

Sub paste_xlPasteFromats()
    ' copy from cell B5 to B10
    Range("B5:B10").Copy
    ' paste to cell B15 to B20
    Range("B15:B20").PasteSpecial Paste:=xlPasteFormats
End Sub

The code uses the Copy method to copy B5:B10, and the PasteSpecial method to paste the copied formatting to B15:B20. The xlPasteFormats argument specifies that only the formatting of the copied cells should be pasted, without affecting data or formulas in the destination cells.

Example 3 – Copy the Cell Background Color and Paste It to the Corresponding Cell in Another Sheet

Enter the code below.

Sub Copy_Cell_Color_to_Another_Sheet()
    Dim sourceSheet As Worksheet
    Dim targetSheet As Worksheet
    Dim sourceRange As Range
    Dim targetRange As Range
    Dim sourceCell As Range
    Dim targetCell As Range
    Set sourceSheet = ThisWorkbook.Worksheets("Copy Cell (Source Sheet)") ' Replace "Copy Cell (Source Sheet)" with the name of your source worksheet
    Set targetSheet = ThisWorkbook.Worksheets("Copy Cell (Target Sheet)") ' Replace "Copy Cell (Target Sheet)" with the name of your target worksheet
    Set sourceRange = sourceSheet.Range("B4:F10") ' Replace "B4:F10" with the range of cells you want to copy
    Set targetRange = targetSheet.Range("B4:F10") ' Replace "B4:F10" with the range of cells you want to paste into
    For Each sourceCell In sourceRange
        Set targetCell = Cells(sourceCell.Row, sourceCell.Column)
        targetCell.Interior.Color = sourceCell.Interior.Color
    Next sourceCell
End Sub

Code Breakdown

Six variables are defined.
The Set statement is used to assign values to the worksheets and range variables.
For Each sourceCell In sourceRange: iterates through each cell in the source range, and assigns the current cell to the sourceCell variable.
Set targetCell = Cells(sourceCell.Row, sourceCell.Column) sets the cell in the target range that corresponds to the row and column in the current source cell.
The interior color of the target cell is set to the same value as the interior color of the source cell.

This macro assumes that source and target ranges are the same size and shape.

Example 4 – Copying the Cell Background Color to Mark Students’ Result

To assign letter grades to students’ marks in a specified range and apply a corresponding color to the grades based on a reference table, use the code:

Sub Copy_Cell_with_StudentMarks()
    For Each markCell In Range("D5:D10") 'change this to the range that contains the student marks
        Set gradeCell = markCell.Offset(0, 1)
        Select Case markCell.Value 'check the value of the mark cell and assign a grade accordingly
            Case Is >= 80
                gradeCell.Value = "A"
            Case Is >= 70
                gradeCell.Value = "B"
            Case Is >= 60
                gradeCell.Value = "C"
            Case Else
                gradeCell.Value = "F"
        End Select
        For Each gradeCell In Range("E5:E10") 'change this to the range that contains the student Grade
            For Each refgradecell In Range("B13:B16") 'change this to the range that contains the grade remark color
                If gradeCell.Value = refgradecell.Value Then
                    gradeCell.Interior.Color = refgradecell.Offset(0, 1).Interior.Color
                End If
            Next refgradecell
        Next gradeCell
    Next markCell
End Sub

Code Breakdown

For Each markCell In Range(“D5:D10”) iterates through each cell in the range that contains students’ marks: “D5:D10“.
The macro uses the Offset property to set the gradeCell variable to the cell to the right of the current markCell. This cell will display the letter grade.
Select Case markCell.Value checks the value of the markCell and assigns a gradeCell. The gradeCell value is set to “A” if the mark is 80 or above, “B” if the mark is between 70 and 79, “C” if the mark is between 60 and 69, and “F” if the mark is below 60.
The Nested For Each loop and the If statement check if the value of the gradeCell matches the value of the current reference grade cell. If there is a match, the interior color of the gradeCell is set to the same value as the interior color of the cell to the right of the current reference grade cell.
The loops continue to the next cell until all marks and grades are assigned and colored.

Example 5 – Copy the Cell Background Color with Matching Data

To get a match in a reference list and apply a color to a range of cells, use the code:

Sub ColorMatch()
    Dim CompanyName As Range
    Set rng1 = Range("C5:C18")
    For Each cell In rng1
        For Each CompanyName In Range("B21:B23")
            If cell.Value = CompanyName.Value Then
                cell.Offset(0, -1).Resize(1, 4).Interior.Color = CompanyName.Offset(0, 1).Interior.Color
            End If
        Next CompanyName
    Next cell
End Sub

This macro compares values in C5:C18 to a reference list of company names and colors in B21:B23. If a match is found, the macro applies the corresponding color to a range of cells to the left of the matched value. The macro uses two Nested For Each loops to iterate through the ranges and an If statement to check for matches.

Frequently Asked Questions

1. How do I fill cells with the background color?

Select Fill Color in Font (Home tab).

3. How do I copy only the background color in Excel?

Select Formmating (R) in Other Paste Options (Home tab).

Download Practice Workbook

Download the workbook and practice.

VBA to Copy Cell Background Color.xlsm

Get FREE Advanced Excel Exercises with Solutions!

Using Excel VBA to Copy the Cell Background Color – 5 Examples