Copying Fill Color ONLY from Conditional Formatting to other cells

[lunsfld]

I have a set of data for which I have set up a rule in Conditional Formatting to apply a 3 color scale based on the cells' values. This works well - the cells' fill colors look right to me.

Now I would like to copy the fill color ONLY from this group of cells to another group (same row/column dimensions).

I tried to write a VBA macro that would copy the ".Interior.Color" property of each cell and paste in the corresponding cell of the second data set but that did not work. The "Interior.Color" property never changed even when the fill color was obviously different. I am assuming this is because Conditional Formatting changes the fill color in a different way than the "Interior.Color" property.

Any ideas how this can be done?

 

[JoeMo]

Have you considered using the same conditional formatting [same format condition(s)] on the second data set?

 

[lunsfld]

The first data set contains the "percent complete" for a bunch of activities in a project I am working on. The second data set contains the date on which the activity started.

I wanted to combine the start date with the color to show start and percent complete in one place. This is an existing report so I can't change the structure too much but I can apply this formatting.

I tried using the "formula" component of Conditional Formatting to apply a format to the dates based on the percent complete but couldn't get it to work either.

Hope that helps.

 

[JoeMo]

The first data set contains the "percent complete" for a bunch of activities in a project I am working on. The second data set contains the date on which the activity started.

I wanted to combine the start date with the color to show start and percent complete in one place. This is an existing report so I can't change the structure too much but I can apply this formatting.

I tried using the "formula" component of Conditional Formatting to apply a format to the dates based on the percent complete but couldn't get it to work either.

Hope that helps.

Are the % complete cells and the date cells on the same sheet?

 

[lunsfld]

Yes sir.

 

[JoeMo]

Yes sir.

Then just select the % cells, right click and choose the format paint brush and apply to the first date cell.

 

[lunsfld]

Then just select the % cells, right click and choose the format paint brush and apply to the first date cell.

I tried that first and unfortunately it doesn't work. It transfers all formatting including changing the date format to %. In addition, it applies the Conditional Formatting to the new data set (which are dates) so the 3-scale fill colors change because the range of dates are different than the range of "% complete" data.

My VBA code would work if I knew how to "unlock" where Conditional Formatting stores the fill color information. As I stated before, it is not in the normal ".Interior.Color" property.

Thanks for your help!

 

[JoeMo]

I tried that first and unfortunately it doesn't work. It transfers all formatting including changing the date format to %. In addition, it applies the Conditional Formatting to the new data set (which are dates) so the 3-scale fill colors change because the range of dates are different than the range of "% complete" data.

My VBA code would work if I knew how to "unlock" where Conditional Formatting stores the fill color information. As I stated before, it is not in the normal ".Interior.Color" property.

Thanks for your help!

VBA stores a FormatConditions object. I think it would be easier to apply the same format conditions you are using for the % cells to the date cells. You can get some help with that here if you post your data layout and the CF formula(s) you are using for the % cells.