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.

Opening of VBA editor in Excel worksheet

Select Insert > Module to open a blank module.

Vba Editor interface

Enter your code.

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

Copy Cell Background Color Using the Interior.Color Property

Use the code:

Code for Copying Cell Background Color Using the Interior.Color Property

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.

Output image of Copying Cell Background Color Using the Interior.Color Property

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

Copying Cell Background Color with xlPasteFormats Property

Use the following VBA code:

Code for Copying Cell Background Color with xlPasteFormats Property

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

Copy the Cell Background Color And Paste It to the Corresponding Cell of Another Sheet

Enter the code below.

Code for copying the Cell Background Color And Paste It to the Corresponding Cell of Another Sheet

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

Copy Cell Background Color for Marking Student 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:

Code image of Copying Cell Background Color for Marking Student Result

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

Copy Cell Background Color with Matching Data

Use the code:

Code for Copying Cell Background Color with Matching Data

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.

Output image of Copying Cell Background Color with Matching Data