Sometimes, you may fill some colors for cells based on certain conditions. You need to copy the filled color from one cell or range to another cell or range in the Excel worksheet. In this post, we introduce two ways to copy the fill color from a cell to other cells: Paste Special and VBA Code. You can choose a way you like to apply it in your Excel worksheet.
Copy Fill Color by “Paste Special” Function
The Paste Special function provides the ability to paste only values, formatting, formulas, etc. In this example, we can apply the Paste Special -> Formatting function to paste the copied color.
Here are the steps:
Step1: Select the cell you want copy the fill color. For example, select cell B3.
Step2: Right click and select “Copy” in the menu.
Step3: Select range F4:F5 you want to fill the color.
Step4: Right click and select “Paste Special” -> “Formatting” in the menu.
Step5: Then cells in the range F4:F5 are filled with the copied color.
Copy Fill Color by VBA Code
If you are skilled and familiar code editing in Excel VBA, you can copy a filled color from one cell and apply it to a range of cells using VBA in Excel. Here’s an example code that copies the color from cell B3 and pastes it to a range of cells from F4 to F5:
Step1: Press Alt+F11 or click Visual Basic in Develop tab to open the Visual Basic Editor (VBE).
Step2:. In the VBE, select the opened sheet you want to run the VBA code, right click and select Insert -> Module in the menu to create a new module.
Step3: In the module, type your VBA code.
Sub CopyColor() ' Copy the fill color from cell B3 Dim myColor As Long myColor = Range("B3").Interior.Color ' Paste the color to the range from F4 to F5 Range("F4:F5").Interior.Color = myColor End Sub
The Interior.Color property is used to get and set the fill color of a cell. In the code above, the color from cell B3 is stored in the myColor variable using the Interior.Color property. Then, the color is pasted to the range F4:F5 using the same property. You can modify the range in the code above to match your specific needs.
Step4: Save the VBA code, close the VBE and go back to the worksheet.
Step5: To run the macro, press Alt+F8 to open the Macro dialog box or click Macros in Developer tab.
Step6: Select the macro you just created and click the Run button.
Step7: Color is copied from B3 and filled to F4:F5 properly.
These two methods are easy to understand and can help us copy colors easily without knowing the RGB of the cell fill color. You can apply either of the methods in this post according to your actual situation.