Programming Cell Color Fill

Copper Contributor

How can i program a cell fill color to match an existing cell color (also include plus possible text)?



The only constant is the B column.  The A column is manually input, and changes with each worksheet use.  The D column is manually input, and changes with each use.

E column is the desired, programmed, output.  

so, in this example, if I type in ORANGES in the D column, would like for the E column to look the same as the corresponding B column.  - adversely, if I were to type in APPLES in the D column (where it currently says ORANGES), then the word CIRCLE, highlighted in Yellow will appear in the corresponding E column.  

Will need this formula to work down the entire E column for about 2,500 rows.

1 Reply


Sub colors()

Dim i, j, k As Long


i = Range("D" & Rows.Count).End(xlUp).Row

For j = 1 To i

k = Application.Match(Cells(j, 4), Range(Cells(1, 1), Cells(10000, 1)), 0)

Cells(j, 5).Value = Cells(k, 2).Value
Cells(j, 5).Interior.ColorIndex = Cells(k, 2).Interior.ColorIndex

Next j

End Sub

You can try these lines of code. In the attached file you can click the button in cell G2 to run the macro.