Traffic light options

[stevejd58]

I have a spreadsheet that runs various macros and formulas and is used by multiple people as an info source and thus protected to prevent meddling and error creep.
My manager wants to traffic light (red, amber, green) a set of text based cells (names of individuals) to indicate outcomes

When the sheet is in protected mode, the ability to shade the cells is greyed out, so what I'm looking for is a way to shade a cell with preset colours whilst the sheet is protected,

I know i could use a separate column with R, A, G text conditionally set using text & fill colours but i don't really have the on screen space and its busy enough already

I've attached an unlocked copy to play with, names are ficticious and its this column that the boss wants to RAG

Any thoughts would be greatly appreciated

[AliGW]

There are instructions at the top of the page explaining how to attach your sample workbook.

[Andy Pope]

could you use Conditional formatting?
Maybe not testing the cells content by test using a formula.

It's not clear what causes a change in the RAG status, especially if the sheet is protected.
If it is a result of some code being run could the code not unprotect the sheet, apply formatting and then apply protection?

[stevejd58]

I had thought of conditional formatting but I can't see how name data would initiate a fill change
The decision to change RAG status is based on a team decision
I know it can be done by unlocking , shading and relocking but the risk of forgetting to protect and the settings being messed up or incorrect paswords being set and no one remembering what they actually inputted is high (creates too much work for me to sort it al out)

[Andy Pope]

I was suggesting the code handled all that, not a user.

You need to explain the workbook you posted. What cells should be filled and under what circumstances?

[AliGW]

Which column is being RAG rated? If this is something that is NOT based on values in the workbook, then you can't use conditional formatting unless you add an editable column somewhere that contains a manually-entered value that triggers the formatting. This might be safer than what you do currently.

[stevejd58]

Sorry, its the patient name column that the boss wants to RAG rate instead of a plain white text filled cell as it is at the present
There are no parameters other than a team based decision as to what RAG rate a person has.

If it wasnt for protection it would be simple to shade but past experience shows that letting nurses with little excel skill on a unprotected sheet makes a lot of work for me

Its a work round for the greyed out shade icon i need whilst retaining the protection

[Andy Pope]

Given there is no logic to the RAG why not protect the sheet but leave that column unlocked? edit: I see you already have that.

I don't have 2016 to check by 0365 protect allows formatting of cells on protected sheet

[AliGW]

OK, so here's what I'd do:

1. Add a very narrow, editable column to the right of the patient name column and apply a drop-down box with a selection of numbers or letters (maybe R, A and G).
2. Set a formula CF rule for each colour along the lines of: =$D5="R" for red, =$D5="A" for amber and =$D5="G" for green.

This way they can't mess anything up and it's a simple mouse click to apply the correct RAG colour.

[AliGW]

Example of what I'm suggesting attached.

[stevejd58]

Thanks, will have a look monday and feedback

[stevejd58]

just to refresh my memory
is that CF - new rule - use formula to determine - =$D5="R" - apply same with A & G
use copy and format paste into the cells of the lower section

[AliGW]

Almost - select C5 - set the rule and use the Applies to box to set the range or ranges to which it needs to apply. This is more reliable, I find. Three rules - one per colour.

[stevejd58]

Worked a treat. Many thanks AliGW

[AliGW]

No problem.