2

I have used conditional formatting to apply a color scale to a range of values. Now I would like to permanently freeze those colors. I have poked around a bit, and can't find event the beginning of a method.

I think of it like paste values. We can either copy and paste formulas, which will be recalculated in their new positions, or we can copy and paste-values - which will just use the previously calculated values and never update them gain.

I want a "paste-values" version of the paintbrush - it would apply the values as they are currently calculated So where a cell previously was bright red because it was the smallest value in my range.

enter image description here

In my first example image, I have four cells, so clearly I could just manually set the colors. In my actual data, I want to copy the colors shown in the first row of my second image down to all the other rows:

enter image description here

And for my next trick, I want to something like that with an even bigger original range, so manually setting colors isn't really an option.

Any ideas? (Excel 2007)

In case anyone is curious, the reason I want to do this is so I can then use the tips here: http://datapigtechnologies.com/blog/index.php/automatically-set-chart-series-colors-to-match-source-cell-colors/ to create a scatter plot in which several dozen lines are given a couple of different smooth gradients - clusters of related lines will be colored similarly, but not identically. Along this theme, but with more lines in a couple of distinct color families:enter image description here

Improve this question

1 Answer 1

Reset to default
2

You will need to use vba for this I believe. It used to be a real pain to get out conditional formatting colours even with vba but they have since added a new function.

The below will copy the colour over in cells A1 to A3. This does not remove the conditional formatting so you will either have to do that manually or add something to the macro to do it.

I haven't really played with it much but it looks like it has to be done on a cell by cell basis (like the macro) and not on the range as a whole.

There is lots of help out there for selecting all the cells you want and looping through them if you are looking for something more automated then this.

Sub test()
For Each c In Range("A1:A3")
    c.Interior.Color = c.DisplayFormat.Interior.Color
Next
End Sub
Improve this answer
2
  • I think that I am still stuck in the "used to be" state, rather than the "since added a new function" state. (Excel 2007 ) msdn.microsoft.com/en-us/library/office/Ff838814.aspx
    – Adam
    Jul 30, 2015 at 20:29
  • Sorry not sure I can help you then, I looked at some of the older code for getting out colours but it relied on having distinct conditional formats and not the colour ranges (which I think were introduced in 2007).
    – gtwebb
    Jul 30, 2015 at 22:12

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .