Excel macro to change cell colour if conditional formatting changes another cell range colour?

Question

I want excel to change a cell colour (within a range) to yellow if conditional formatting switches another cell from a range to yellow. Can this be done with a macro?

Answer 1

Excel Macro Conditional Formatting

Answer 2

Excel VBA cannot parse fill colors set by conditional formatting. So, the answer to your basic question is 'No'. A macro/event handler cannot change the fill color of one cell based on the conditionally applied fill color of another cell.



You would have to structure your macro or event handler to use the same test used for conditional formatting of the primary cell. Then apply the fill color of the secondary cell based on the result of the conditional format test applied to the primary cell.

Answer 3

Why can't you just enter a conditional format formula in the G* cells that says if(K*>0) to trigger the formatting.





Think you could conditionally format the other cell as well.



=Cell("color",A1) would return a 1 if the value is formatted as red, and a 0 if it had no color formatting. So maybe as a conditional format formula:



=if(Cell("color",A1>0),1,0)



I don't have excel at home to test it for you though.



EDIT-EDIT- So your first cell would change its own color based on the conditional format there (I guess tied to the value the in-cell formula produces). Then in the other column your conditional format formula would be referenced to the cell properties for color in the first.

Answer 4

Ms Excel does not consider changing a cell's color to be significant to calculation, and therefore will not necessarily recalculate a formula when a cell color is changed. As this is the case in your case, it is not possible.