Copy automatically only the color from one cell to another- EXCEL VBA

Question

I have cell 017, the value and color of this cell is changing constantly due to a formula and conditional formatting.

Cell O16 changes constantly due to a formula too but the formula and their values are different from cell 017.

What I want is that cell O16 contains the same color of cell O17 automatically once the value of cell 017 has changed.

There should be a solution in VBA. I hope your help.

EDIT:

User simoco has came up with a solution for Excel 2010 and later:

Private Sub Worksheet_Calculate()
    Range("O16").Interior.Color = Range("O17").DisplayFormat.Interior.Color
End Sub

Unfortunately I use Excel 2007, Is there and equivalent to that code in 2007?.

Dmitry Pavliv Dmitry Pavliv · Accepted Answer · 2014-03-06T15:35:48

Works only with Excel 2010 and later

If your cell O17 can be changed only through formula, use following code (this code should be used in Sheet module):

Private Sub Worksheet_Calculate()
    Range("O16").Interior.Color = Range("O17").DisplayFormat.Interior.Color
End Sub

Otherwise, also add Worksheet_Change event with the same code.