How to Automate coloured text in a Excel SpreadsheetI had a Excel spreadsheet that I wanted the result of "K22" to show as either "
Red or
Blue" depending upon the result of another cell.
Basically:
K19 = a Fixed figure of £70.88
K15 = the total of other columns. (Expense's)
K22 = the sum of (K15 minus K19) (Income)
So, what I wanted was:
If K22, is
less than K19, then the result to be shown in
BLUE, or, the result to be shown in
RED if
more than K19
Red for "Overspent" and Blue for "Under-spent"
Conditional formating is what was required.
"Conditional Formating" can be found in the ""Format" menu of Excel from the menu bar.
On the format menu from the first drop-down box select "
Formula Is" then type =K22<K19
Then choose the "
Format" button because you need to chose a font (Regular, Bold etc), and font colour so now chose "
Blue"
Important don't forget to include the "
= " (equals sign) at the start of the formula's (without the quotes)
Now click "
add", again first choosing the "
Formula Is" option, now put in the second condition =K22>k19
Followed again by the "
Format" button, because you now need to chose a second font and font colour etc, so now chose "
Red"
However, there are also a few other choices you can use from the drop-down menu's if you are not sure of what to type in the box, none of which are difficult once you know what you needed to use to begin with and how to get to "Conditional Formating" and not forgetting the basics.
However, I found by clicking the small coloured box on the right of the text typing box that I could then select the cell/s with the mouse instead of typing them. (Which for me worked out better and easier the first time)
Obviously in your worksheet you will be using different cell numbers, so you need to adjust this accordingly.
Just as a matter of interest:
The > sign means "More than"
The < sign means "Less than"
Original solution provided by:
Exo & DaveS from:
http://forum.kitz.co.uk/index.php?topic=1436.0