VBA: Mimic Conditional Formatting Colour Scales in Adjacent Column

Unless the cells in column B match those in column C, you can't make the colour scale work on them.

This is a limitation of the way that colour scales work.

You may be able to do it with VBA - is that an option for you? Is it REALLY necessary for column B to have the shading? The more CF you have, the slower your workbook will become.

OK - I will move you to the VBA section and hopefully one of the coders will help you out.

This is what I've come up with... It doesn't work - any suggestions please?

Please Login or Register  to view this content.

Administrative Note:

We would very much like to help you with your query, however you need to include code tags around your code.

Please take a moment to add the tags. Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(Note: this change is not optional. I will add them for you this time.)

My understanding is that VBA cannot detect colour applied via conditional formatting. What you will need to work with is the VALUE of the cell containing the CF shading.

It's not my forum.

Did you understand my comment about CF colouring?

Use this UDF to get the color reference and then just set the next column to same

Please Login or Register  to view this content.

use as " = getcolor(the cell you select)"


EDIT: Just tried tried it - doesn't recognise shading

Finally found a way to do this

Please Login or Register  to view this content.

found code from Paul Kelly

Please Login or Register  to view this content.