dynamic cell updates based on highlight colour of previous cell

Hi, Hoping someone can help with this (I am not very good in VBA or Excel advanced things sadly). I am trying to automate a horribly manual process before handing over the process on 30th June. In the attached file I want to amend the highlight colour in Cell A - this would then update the value in Cell B which in turn would update Cell C. I cannot get the value to change when I update the highlighting Cell A. I have added some Names to Cols A to C, but don't know if I need them.

This bit of VBA is also there to be used in Col B.


Function ColourIndex(CellColor As Range)

Application.Volatile True

ColourIndex = CellColor.Interior.ColorIndex

End Function

It is driving me mad, so if anyone has any ideas please let me know ASAp.

Thanks,

Andy