View Full Version : Spreadsheets

24-01-2004, 09:01 AM
In a spreadsheet I would like to have a cell change colour if its value exceeds a certain value. How can I do this?

24-01-2004, 09:33 AM
I am sure we've seen this one before, and the answer is no (as far as I am aware).
I would go for something like two columns, and an IF statement like this:
=IF(C3>100,C3,"") in one column and =IF(C3<=100,C3,"") in the other. Then, all values over a certain amount would be in a different column.
You can colour based on negative, positive, and zero using formatting, but that's it.

24-01-2004, 09:34 AM
If its ms excel, probably other spreadsheets operate similarly, go to the format menu - select conditional formatting - there's a fairly obvious range of options on how to do what you want - you would want the patterns format to change the cell colour.

24-01-2004, 09:43 AM
Have a look at "Format / Conditional Formatting". You can change colours using this. You can also apply more than one conditional format to a cell / range, for real fancy effects :|

24-01-2004, 10:02 AM
Oh, there you go, I knew that was there but never thought of it.

24-01-2004, 10:28 AM
However, just for interest I had a look at an old (5 yrs) version of Lotus 123 that I still have on a machine, and it doesn't seem to have this facility.

Changing colours based on cell content seems to require writing a bit of code for that particular spreadsheet program.

While Excel is virtually the default spreadsheet these days, others do have their points, and its probably a good idea to say just what spreadsheet program you are using:

It all started with VisiCalc many years back ..............

24-01-2004, 12:35 PM
Hi as the others have stated Conditional Formatting is the way to go.

1. Select the cells you want to be tested whether its over a certain value
2. Select Format|Conditional Formatting
3. Select the options Cell Vlaue Is / Greater Than (or Greater than or equal to) and in the last box enter the amount
4. Click the Format box and choose your formatting - colours are the patterns if you want to colour the interior of the cell.
5. Click OK

You can choose up to 3 conditions on when a cell is formatted. The cells that get formatted are the ones where the condition is true.

If you need to do more that 3 conditions it is not possible via conditional formatting but it is possible via code.


24-01-2004, 05:09 PM
This is what i look at forum for the functions of programs u've used for years and never known about. conditional formatting looks like it could be very useful for me too.
Thanks Guys!!!

24-01-2004, 09:15 PM
To Robbo, Ruqila, Wuppo, Parry & Pjg54

Thanks, guys. I am using Ability Office, which doesn't have conditional formatting, but I am writing the file for use in excel. When I can get to the machine in question, I shall try your suggestions. Many thanks :-)))