Using Graded Color Scale Conditional formatting for cells surrounding the value itself

[jolarrett]

Hi

I am an analyst/accountant for a UK retailer.

What I have is a set of variances to forecast which I have developed into a variance "wall". Each brick in the wall has the title of the area it represents as well as:

Actuals
Budget
Variance

I like to use the conditional formatting graded colour scale as a quick way to draw the user's eye to the highest gains/losses. However, I would like the entire brick to be the same colour, not just the variance cell itself. Is there any way to use the graded color scale to colour in cells surrounding the value as well as the value itself? Am happy to e-mail a picture if you would like to see the file.

Thanks in advance

Jo

 

[CROY1985]

Please post a picture, including how you want it to look.
1) it will help us find a solution
2) it sounds like you've a really interesting way to display variances and i'm going to take notes!

 

[jolarrett]

I can't work out how to paste a picture on here :-S can I e-mail it to you?

 

[Zakkaroo]

Not sure if this is what you mean, but you can use Conditional formatting to colour other cells, based on the contents of just one cell.

e.g. Lets say you had data in cells A1 to E10, but you wanted B, C and D on the same row to be formatted red, if the value in C was equal to 10.

Highlight B1 to D10
Conditional Formatting
Use a formula to determine which cells to format
Formula: =$C1=10

Now if C4=10 then B4, C4 and D4 will be red

Is that what you're after?

 

[jolarrett]

Hi, thanks for the reply. It is kind of what I'm after but I want the colour ribbon/colour scales available in Excel 2013 to determine the colour based on the values of all of the cells. I love the colour ribbons because whenever the data is refreshed the colours are refreshed based on their scale in comparison to all of the numbers. So the very best result would be bright green and the very worst result would be dark red with all of the rest of the numbers somewhere in between.

 

[RoryA]

Hi, and welcome to the forum.

Unfortunately there is no easy way to do this. The CF scales only work off the values of the cells where the formatting is being applied - you can't use a formula with them. It is possible to use code to colour the cells using a worksheet event (Change or Calculate depending on the situation) - if you'd like to pursue that, we'd need to know which ranges need to be coloured, and which determine the colours for each row.