|The problem with doing what you ask with VBA is that a cell filled with a color via Conditional Formatting is not recognized as being filled by the CF color when checked with VBA.|
Conditionally Format A1 to be any color except Yellow when it equals 0.
Leave A1 empty and it should turn the color you chose.
Now run this code:
[A1].Interior.ColorIndex = 6
The first MsgBox should read -4142 which means the cell is not filled.
The second MsgBox should read 6 because the code colored the cell Yellow, even though it's still the same color as before due to the CF.
Run the code again and it'll return 6 both times since it now thinks that the cell is filled with yellow, even though it'll only be yellow when the CF condition is false.
One way to have VBA color a cell to match the color of another cell which was colored by CF is to basically do the same thing you did manually: You have to have VBA check the data in the CF'd cell(s) and fill the desired cell with the matching color.
There are methods for returning the color of a cell filled via CF and I guess you check each cell in your range until you find a Red, if not check them again looking for Yellow, etc. Once a desired color was found, you could color the cell in column A to match.
If you Google something like this, you'll get lots of hits on how to return the color of a cell that was filled via CF:
vba to check color of conditional formatting
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.