Risk formatting on a single Cell

[Chris Waller]

Hi everyone,

I am using Excel 2010 and I would like to be able to colour a cell in relation to the risk status i.e. red, red/amber, amber/green, green and the shades in between each. I do not want to put any text in the cells, I would just like a way of being able to change the colour of the cell using something like a slider or any other way, whichever is easier and does the job. Any help would be greatly appreciated. If the result necessitated using VBA, I would like to be spoon fed as I am not very good with VBA. TIA

 

[DRSteele]

I'm not sure about Excel 2010, but this can easily be done in the up-to-date version of Excel by using Conditional Formatting and a Scroll Bar.

It sounds like you want a nearly continuous gradation of colours from red to green. I chose 10 steps to correspond to your risk statuses.

In cell A1 I put a number between 1 and 10, like 7. Go to Conditional Formatting and create a New rule. Format cells based on their values. Format style 3-colour scale. Select Type number for Minimum, Midpoint and Maximum. Enter 1 for Minimum, 5 for Midpoint and 10 for Maximum and select red, amber, green, respectively. Select Ok all the way out. Change the number in A1 to see the fill-colour of A1 change.

Select Developer, Insert and under Form Control select Scroll Bar (Form Control). Draw the scroll bar horizontally next to cell A1. Right-click, Format control, Minimum 1, Maximum 10, Increment 1, Page 1, Cell link click on cell A1. Ok.

To hide the number in A1, select A1, right-click , Format Cells, Number, Category, Custom, and type three semicolons in the Type box ;;; and then Ok.

Presto! Adjust the slider to alter the number in A1 (which is now hidden) and the fill-colour of A1 will go in 10 steps from red through amber to green.

 

[Chris Waller]

DRSteele,

That is brilliant. Thanks so very much for the help. You have been a lifesaver. Thanks again.

Chris

 

[DRSteele]

You're welcome. I'm glad it worked for you.

 

[Chris Waller]

You're welcome. I'm glad it worked for you.

DRSteele,

Thanks for your prompt response and it will work, however, before I use your conditional formatting, I have just one question. That is, is it possible to do something similar on an empty cell? The reason I ask, is because If someone wants to put some text in the cell how would I get this to work as in your suggestion there has to be a number in the cell

TIA Chris

 

[DRSteele]

You stated "I do not want to put any text in the cells." But now you do. Ok, the bad news is that this cannot be done using Colour Scales. What you'll have to do is create a set of CndFrmt rules that cover all the colour gradations you wish to have. So if you want 10 colours you'll need to create 9 rules (leaving the formatting of the cell to be one default colour at the end of the scales, like green). You can create those rules that refer to other cells by completing Use a formula to determine which cells to format. The cell your Rules refer to can be something like B1, on top of which you can put the scroll bar that links to it.