| Forum Home | ||||
| Press F1 | ||||
| Thread ID: 41843 | 2004-01-23 20:01:00 | Spreadsheets | Phil1 (194) | Press F1 |
| Post ID | Timestamp | Content | User | ||
| 209583 | 2004-01-23 20:01:00 | In a spreadsheet I would like to have a cell change colour if its value exceeds a certain value. How can I do this? | Phil1 (194) | ||
| 209584 | 2004-01-23 20:33:00 | Phil 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. robo. |
robo (205) | ||
| 209585 | 2004-01-23 20:34:00 | 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. | rugila (214) | ||
| 209586 | 2004-01-23 20:43:00 | 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 :| | wuppo (41) | ||
| 209587 | 2004-01-23 21:02:00 | Oh, there you go, I knew that was there but never thought of it. Whoops. robo. |
robo (205) | ||
| 209588 | 2004-01-23 21:28:00 | 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 .............. |
rugila (214) | ||
| 209589 | 2004-01-23 23:35:00 | 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. hth |
parry (27) | ||
| 209590 | 2004-01-24 04:09:00 | 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!!! |
pjg54 (2639) | ||
| 209591 | 2004-01-24 08:15:00 | 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 :-))) |
Phil1 (194) | ||
| 1 | |||||