Conditional Formatting: Color Scales based on external range

[George]

Hi Ninjas,

Bit of an odd one for you:
I've been asked to put together a document that shows the price of a set of items in a few different markets, and to conditionally format the cells containing the price based on their sales in the corresponding market, specifically using a color scale rather than setting up a few bands and conditional formatting based on that.

Sounds simple, but I can't find the settings anywhere - is this something that's possible, or am I going to have to spoof the functionality of color scales with a bunch of format rules with IF statements/write my own conditional formatter with vba?

[EDIT: Office version I'm using is 2016]
Thanks,

George.

 

[chirayu]

Do you have a sample file? with rules for conditional formatting

 

[George]

chirayu: basically I want to format the values in Market 1 Price, Market 2 Price and Market 3 Price etc. based on the value of Market 1 Sales, Market 2 Sales and Market 3 Sales etc. using a color scale, but I can't figure out how to get it to reference external ranges for a color scale.

Thanks,

George.

 

[r1c1]

This is not possible with conditional formatting alone.

One option is to mix CF with a picture link, but this may not look crisp and doesn't work well when printing.

I think using VBA (or a lot of CF rules) is the easiest option here.

 

[George]

r1c1: cheers .

George.

 

[p45cal]

This is not possible with conditional formatting alone.

It seems so. In the attached, alter the conditional formatting on the Sales sheet to your liking then go to the SalePrice sheet and click the button. This is the macro that runs:

Sub blah()
For Each cll In Sheets("SalePrice").Range("B2:M4").Cells
  cll.Interior.Color = Sheets("Sales").Range(cll.Address).DisplayFormat.Interior.Color
Next cll
End Sub

It's simple at the moment because you have the corresponding cell containing corresponding data on the two sheets. I imagine this might not always be the case!
Also it just copies the colour across, no TintAndShade, Color, Pattern etc. which might be needed with other conditional formats and/or Excel 2016 (I'm using 2010).

Whoever asked you to do this might not want to have a warning about macros when opening the book, so once you've run it you can delete the macro and save as a .xlsx file. The corollary is that the colouring is not dynamic, although (by keeping macros and a tweak or two) it could be.

 

[George]

Thanks p45cal - I'm just going to vba the whole thing and provide the end user with a static "output" file that's formatted the way they want it (my vba is alright so it's not a problem, just a shame the stock system isn't ideal).