how to preserve fill color when hiding/unhiding column with vba

[steve harris]

When "No" appears in a cell of a dynamic range I set the fill color to red in vba. A macro gives the option of hiding/unhiding the column containing this range. On unhiding the column, red is gone. How do I preserve the fill color without having to go through the range cell-by-cell on unhiding?

I am using Excel 2007.

(I notice that the same thing happens if I hide / unhide a column from the ribbon.)

 

[steve harris]

****SOLVED****

Problem was not what I thought. Symptoms I stated are incorrect. I was re-setting Interior.ColorIndex in another sub. My apologies.

When "No" appears in a cell of a dynamic range I set the fill color to red in vba. A macro gives the option of hiding/unhiding the column containing this range. On unhiding the column, red is gone. How do I preserve the fill color without having to go through the range cell-by-cell on unhiding?

I am using Excel 2007.

(I notice that the same thing happens if I hide / unhide a column from the ribbon.)