Using the colour scale conditional formatting | Excel forum

Resolved · Medium Priority · Version 2010

Fri 23 Feb 2018, 14:38 Delegate Jen said...

Jen has attended:
Excel Advanced - Formulas & Functions course
Excel Forecasting and Data Analysis course

Using the colour scale conditional formatting

Hi
I have applied a 3-colour colour-scale conditional format rule to a row of data. But I would now like the cells in the row above to take on the colour of the corresponding cell below, even though the cell above has a different value in it. Is this possible? I should mention the cells in the above row contain IF functions.
Many thanks
Jen

Fri 23 Feb 2018, 16:49 Trainer Jens said...

RE: Using the colour scale conditional formatting

Hi Jen,

Thank you for the forum question.

Colour scales (heat maps) are difficult to apply to another range (row).

If you had 3 rules, which added 3 different colours to the row it would be easy to get the same 3 colours in the row above. Relative cell references cannot be used in colour scales rules and it is a huge limitation.

It can be done coding Excel using VBA, but I do not know if you have VBA knowledge.

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector