Marking cells Good-Bad-Neutral
Sometimes when you are reviewing a file against a printed report, you may need to identify when cell values are correct, wrong or close.
Consider using the built-in Styles Good/Bad/Neutral on the Home ribbon tab – see below.
These can be quicker than using the usual fill colour icon.
Remember they may not be useful for colour blind readers if you are sharing with other people.
Don’t forget once applied if you want to apply the same format to another cell press the F4 function key.
Once formatted you can sort or filter by colour – see right click options below.
Hello, I was wondering if there was a way to check if a cell had one of these conditions. If a cell is bad, I want to do a certain thing to it. How would this be done?
No you can’t check these formats in a formula. You can in a filter, but not a format. You need to use a cell entry and then a formula to identify the condition. Eg enter 0, 1 and 2 in cell next to the cell involved and then use an IF function to do a different thing.
You could use a filter on the range for a colour and then use CTR + Enter to enter the same value in a range of visible cells next to the coloured cell.
Cell color is a cell property, so you can refer to it in a logical statement in VBA.
However, unless you are picking “bad” results arbitrarily, it is usually easier to us a formula to check if the cell is “bad,” then use conditional formatting to color it that way. The “bad” format is actually the default style for conditional formatting checks.
Hi Jonathon
Yes, if a conditional formatting rule can be used it is better to use that. But if you are checking a sheet against an external source it might not be possible. This post is about a manual solution that can then be used with the colour filter feature.
Regards
Neale
Great tip!
I want the ‘good/bad/neutral’ coloumn to display in the commands section inherently instead of having to go to Cell Styles>Good/Bad/Neutral>select option.
Is there a way to do that?
Cheers!
You could set up conditional formatting to automate the formats.
Do you know if there is a keyboard shortcut for marking selected cells Good / Bad / Neutral?
Probably need a macro to automate it.
Hi Neale,
is there a way that you can add all the bad and the neutrals and as you change the bad to a neautral that it automatically add it to the neutrals?
Hi Bonita
To add up the same colour you can use filters and SUBTOTAL.
SUBTOTAL only adds up the visible cell in a filtered list. So if you filter the list by the neutral cell it will only add up those visible cells.
If you change a colour you will need to re-apply the filter.
There is no built-in function to add up based on a colour.
Regards
Neale