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?.

Solution

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.

Comments

[CreamStat]

I've tried your code but there's a message of error and that should be the reason of my problem. I use Excel 2007 in Spanish, the message says:" Error "4838" has produced in running time. The object doesn't accept this property or method".

[Dmitry Pavliv]

as Tim mentioned, DisplayFormat was introduced only in Excel 2010

[Dmitry Pavliv]

I'm trying to find it out, but not sure.. why not just add CF rules for O16 same as for O17?

[CreamStat]

The values for O16 are different. So, the color would be different for CF rules.

[Dmitry Pavliv]

let us continue this discussion in chat