Auto update colour of cells referenced from another sheet

[Goddess]

Hi,

I tried to use VBA code to reference a cell colour from another sheet. Eg, Sheet1 is the main sheet, where I will change the colour of certain cells as and when required. Sheet2, Sheet3, etc will contain the data, where some cells will reference back to Sheet1 (using formula =Sheet1!$A$10, for example) and the following VBA code to reference the colour as well.

Private Sub Worksheet_Calculate()
'apply cells colors from single-cell formula dependencies/links
Dim Cel As Range
Dim RefCel As Range
On Error Resume Next
For Each Cel In ActiveSheet.UsedRange
If Cel.HasFormula Then
Set RefCel = Evaluate(Mid(Cel.Formula, 2))
Cel.Interior.Color = RefCel.Interior.Color
End If
Next Cel
End Sub

However, when I change the colour in Sheet1, the other sheets does not automatically update the colours. I will need to 'run' the code every time I change the colour. Am I doing anything wrong?

Also, is the any simpler way of doing this, maybe conditional formatting, etc? I'm not very good in VBA. Also, it states that I need to save the file in macro supported Excel file. I'm worried my co-workers can't open the file when I send to them.

Thanks!