Conditional Formatting like Color Scale but doing it via Interior.Color

Hi,

I'm trying to add some static Interior.Color formats to cells but in the format of a color scale, the same as what conditional formatting would do but without the use of conditional formatting, by doing this it would make the cells 100% mobile compatible (compatible with Goggle sheets and other apps that disallow conditional formatting) aswell as making it faster, smaller (in file size) and won't change if you remove the any rows or change some values.

However, you could use it as a backup "underlay" where by a backup color scheme would be the static formatting under the conditional formatting which would only show on apps that disallow conditional formatting, so basically turning this into a fallback.

An example set of data to use is the following CSV data which will help explain what I am trying to achieve.

Please Login or Register  to view this content.

If you were to run the macro below would see the output I am wanting to achieve but the problem is this route is using conditional formatting, I am wanting to do it WITHOUT conditional formatting.

Please Login or Register  to view this content.

After running it, you'd see the following:

Vqp6leQ.png


The colors that I would want the scale to be based on are:

GREEN: 99/190/123 (RGB) or 8109667 (Interior.Color)
YELLOW: 255/235/132 (RGB) or 8711167 (Interior.Color) (Mid-point)
RED: 248/105/107 (RGB) or 7039480 (Interior.Color)

I've found the following: http://www.cpearson.com/Excel/cformatting.htm but unfortunately it isn't compatible with Excel 2007 onwards (I am using Excel 2013), If the conditional formatting colors can be detected then a "hacky" workaround would suffice but it's very much possible (although I don't know how) to do it with a histogram.

This request could be very much an assest to other VBA and excel users as some people might not want colors to change if values are changed or rows are removed and also anyone opening sheets with this requested color scale formatting would be able to view the colors on/in services and apps like Google Sheets which strips out all conditional formatting and thus leaves the page completly blank which is far from ideal. I'm surpised Excel didn't include an easy "Convert to Static Formatting" or a fallback option like this for conditional formatting.

Hopefully someone can help and has an idea or method of doing this, it would be much appreciated.

See attached for example data + another description of how/what I want to achieve.

Oh wow, I didn't know about the "DisplayFormat" function, that is awesome!

Works perfectly, thank you. I'm impressed!