| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
conditional format range set-up in Excel 2000
I have a spreadsheet which I require the numbers 1-300 to be green, 301-800
to be orange and anything 801 and in excess to be red. The only way I seem to be able to do this is by establishing a sheet, which I hide within my worksheet, listing consecutive numbers in 3 separate columns and naming green, orange and red and using the formula is aspect of conditional formatting along with a countif formula. Is there an easier way of doing this, as there are occasions when the ranges are changed? Many thanks for any help offered. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
conditional format range set-up in Excel 2000
Hi Ellie
Mark the range of cells you wish the formatting to apply to FormatConditional FormattingCell value ischoose from dropdown Greater than800 set Format Red Add Repeat using Greater than 300 Format Orange Add Repeat using Greater than 0 Format Green -- Regards Roger Govier "Ellie" wrote in message ... I have a spreadsheet which I require the numbers 1-300 to be green, 301-800 to be orange and anything 801 and in excess to be red. The only way I seem to be able to do this is by establishing a sheet, which I hide within my worksheet, listing consecutive numbers in 3 separate columns and naming green, orange and red and using the formula is aspect of conditional formatting along with a countif formula. Is there an easier way of doing this, as there are occasions when the ranges are changed? Many thanks for any help offered. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
conditional format range set-up in Excel 2000
Thank you Roger, it worked great. Unfortunately the powers that be have just
come along and requested a change of criteria. The request is now as follows:- If a figure less than 0, to be yellow, but when blank to be white. I have used your information provided earlier to allow for anything less than 100 to be orange and anything less than 300 to be red. Many thanks. Ellie "Roger Govier" wrote: Hi Ellie Mark the range of cells you wish the formatting to apply to FormatConditional FormattingCell value ischoose from dropdown Greater than800 set Format Red Add Repeat using Greater than 300 Format Orange Add Repeat using Greater than 0 Format Green -- Regards Roger Govier "Ellie" wrote in message ... I have a spreadsheet which I require the numbers 1-300 to be green, 301-800 to be orange and anything 801 and in excess to be red. The only way I seem to be able to do this is by establishing a sheet, which I hide within my worksheet, listing consecutive numbers in 3 separate columns and naming green, orange and red and using the formula is aspect of conditional formatting along with a countif formula. Is there an easier way of doing this, as there are occasions when the ranges are changed? Many thanks for any help offered. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
conditional format range set-up in Excel 2000
Sorry, Roger. A slight misunderstanding on the new criteria.
If less than 0 or = 0 to be yellow, but if cell is blank to show no format. Now also anything less than 100 to be yellow and less than 300 to be red. Haven't found a way round the blank cell having no format, but knowledge is limited in this area. Many thanks. Ellie "Ellie" wrote: Thank you Roger, it worked great. Unfortunately the powers that be have just come along and requested a change of criteria. The request is now as follows:- If a figure less than 0, to be yellow, but when blank to be white. I have used your information provided earlier to allow for anything less than 100 to be orange and anything less than 300 to be red. Many thanks. Ellie "Roger Govier" wrote: Hi Ellie Mark the range of cells you wish the formatting to apply to FormatConditional FormattingCell value ischoose from dropdown Greater than800 set Format Red Add Repeat using Greater than 300 Format Orange Add Repeat using Greater than 0 Format Green -- Regards Roger Govier "Ellie" wrote in message ... I have a spreadsheet which I require the numbers 1-300 to be green, 301-800 to be orange and anything 801 and in excess to be red. The only way I seem to be able to do this is by establishing a sheet, which I hide within my worksheet, listing consecutive numbers in 3 separate columns and naming green, orange and red and using the formula is aspect of conditional formatting along with a countif formula. Is there an easier way of doing this, as there are occasions when the ranges are changed? Many thanks for any help offered. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
conditional format range set-up in Excel 2000
Hi Ellie
No problem. Repeat the procedure for setting Conditional formatting, and here I am assuming the first cell marked in your range is A1. Change the cell reference below to whatever is the first cell for you. This time we are going to use the dropdown on Cell Value is to select "Formula is" each time instead of "Cell Value" Condition 1 Formula is =AND(A1<"",A1<=0) Format Yellow Condition2 Formula is =AND(A1<"",A1<=100) Format Orange Condition3 Formula is =AND(A1<"",A1<=3000) Format Red -- Regards Roger Govier "Ellie" wrote in message ... Sorry, Roger. A slight misunderstanding on the new criteria. If less than 0 or = 0 to be yellow, but if cell is blank to show no format. Now also anything less than 100 to be yellow and less than 300 to be red. Haven't found a way round the blank cell having no format, but knowledge is limited in this area. Many thanks. Ellie "Ellie" wrote: Thank you Roger, it worked great. Unfortunately the powers that be have just come along and requested a change of criteria. The request is now as follows:- If a figure less than 0, to be yellow, but when blank to be white. I have used your information provided earlier to allow for anything less than 100 to be orange and anything less than 300 to be red. Many thanks. Ellie "Roger Govier" wrote: Hi Ellie Mark the range of cells you wish the formatting to apply to FormatConditional FormattingCell value ischoose from dropdown Greater than800 set Format Red Add Repeat using Greater than 300 Format Orange Add Repeat using Greater than 0 Format Green -- Regards Roger Govier "Ellie" wrote in message ... I have a spreadsheet which I require the numbers 1-300 to be green, 301-800 to be orange and anything 801 and in excess to be red. The only way I seem to be able to do this is by establishing a sheet, which I hide within my worksheet, listing consecutive numbers in 3 separate columns and naming green, orange and red and using the formula is aspect of conditional formatting along with a countif formula. Is there an easier way of doing this, as there are occasions when the ranges are changed? Many thanks for any help offered. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
conditional format range set-up in Excel 2000
Thank you so much Roger.
Ellie "Roger Govier" wrote: Hi Ellie No problem. Repeat the procedure for setting Conditional formatting, and here I am assuming the first cell marked in your range is A1. Change the cell reference below to whatever is the first cell for you. This time we are going to use the dropdown on Cell Value is to select "Formula is" each time instead of "Cell Value" Condition 1 Formula is =AND(A1<"",A1<=0) Format Yellow Condition2 Formula is =AND(A1<"",A1<=100) Format Orange Condition3 Formula is =AND(A1<"",A1<=3000) Format Red -- Regards Roger Govier "Ellie" wrote in message ... Sorry, Roger. A slight misunderstanding on the new criteria. If less than 0 or = 0 to be yellow, but if cell is blank to show no format. Now also anything less than 100 to be yellow and less than 300 to be red. Haven't found a way round the blank cell having no format, but knowledge is limited in this area. Many thanks. Ellie "Ellie" wrote: Thank you Roger, it worked great. Unfortunately the powers that be have just come along and requested a change of criteria. The request is now as follows:- If a figure less than 0, to be yellow, but when blank to be white. I have used your information provided earlier to allow for anything less than 100 to be orange and anything less than 300 to be red. Many thanks. Ellie "Roger Govier" wrote: Hi Ellie Mark the range of cells you wish the formatting to apply to FormatConditional FormattingCell value ischoose from dropdown Greater than800 set Format Red Add Repeat using Greater than 300 Format Orange Add Repeat using Greater than 0 Format Green -- Regards Roger Govier "Ellie" wrote in message ... I have a spreadsheet which I require the numbers 1-300 to be green, 301-800 to be orange and anything 801 and in excess to be red. The only way I seem to be able to do this is by establishing a sheet, which I hide within my worksheet, listing consecutive numbers in 3 separate columns and naming green, orange and red and using the formula is aspect of conditional formatting along with a countif formula. Is there an easier way of doing this, as there are occasions when the ranges are changed? Many thanks for any help offered. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel 2000 macro for page format slow | Excel Discussion (Misc queries) | |||
| copy with format | New Users to Excel | |||
| Excel 2000 worksheet where I want to convert date format | Excel Discussion (Misc queries) | |||
| Save in Multiple format crashes Excel 2000 SP3! | Excel Discussion (Misc queries) | |||
| Excel 2002 and 2000 co-install. Control Which Starts ? | Excel Discussion (Misc queries) | |||
Linear Mode
