If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Conditioning formating a data value to change to a color
Hello,
What I am trying to do is lets say I have a bunch of numbers in column F (for illustration purposes I will only show one number) and what I would like if any of the numbers in column F are greater than 300 I want it to change the color of that number to red automatical (See example) date time day evening night 24hr. Totals 3/12/04 8:00 100 12:00 300 4:00 600 1000 (change to red) I have no problem with changing the number to red manually but it gets to be a pain in the neck when the spreadsheet gets really long. Any help to this would be appreciated. |
#2
|
|||
|
|||
Conditioning formating a data value to change to a color
Steve, highlight Column F (click the column heading at the top of the
worksheet), then pull down the Format menu and select Conditional Formatting. Your condition is "Cell value is greater than 300." Click the Format button, select the Font tab, select red for the font color, then click OK, and OK again. -- DDM "DDM's Microsoft Office Tips and Tricks" Visit us at www.ddmcomputing.com "steve" wrote in message ink.net... Hello, What I am trying to do is lets say I have a bunch of numbers in column F (for illustration purposes I will only show one number) and what I would like if any of the numbers in column F are greater than 300 I want it to change the color of that number to red automatical (See example) date time day evening night 24hr. Totals 3/12/04 8:00 100 12:00 300 4:00 600 1000 (change to red) I have no problem with changing the number to red manually but it gets to be a pain in the neck when the spreadsheet gets really long. Any help to this would be appreciated. |
#3
|
|||
|
|||
Conditioning formating a data value to change to a color
Hello,
Thanks for your reply. I just wanted to say that it works but there is one minor problem which I forgot to explain. In my example: date time day evening night 24hr. total (no red) 3/15/04 8:00 100 3/15/04 12:00 300 3/15/04 4:00 600 1000 (red) date time day evening night 24hr. total (no red) 3/16/04 8:00 100 3/16/04 12:00 300 3/16/04 4:00 600 1000 (red) I don't want any of the column headings (24hrs. total to be included in red). I only want the numbers. DDM wrote in message ... Steve, highlight Column F (click the column heading at the top of the worksheet), then pull down the Format menu and select Conditional Formatting. Your condition is "Cell value is greater than 300." Click the Format button, select the Font tab, select red for the font color, then click OK, and OK again. -- DDM "DDM's Microsoft Office Tips and Tricks" Visit us at www.ddmcomputing.com "steve" wrote in message link.net... Hello, What I am trying to do is lets say I have a bunch of numbers in column F (for illustration purposes I will only show one number) and what I would like if any of the numbers in column F are greater than 300 I want it to change the color of that number to red automatical (See example) date time day evening night 24hr. Totals 3/12/04 8:00 100 12:00 300 4:00 600 1000 (change to red) I have no problem with changing the number to red manually but it gets to be a pain in the neck when the spreadsheet gets really long. Any help to this would be appreciated. |
#4
|
|||
|
|||
Conditioning formating a data value to change to a color
2 ways you can try:
A. Remove the conditional formatting [CF] for the headers Select any empty cell (which is without the CF) *Double-click* on the Format Painter button ("brush" icon) Now just click on each of the header cells in col F in turn to "paint" over and remove the CF When done, press Esc to revert the cursor to normal B. Change the conditional formatting [CF] to one using "formulas" instead Select col F Click Format Conditional Formatting Under "Condition 1", make the settings: Formula Is | =AND(ISNUMBER(F1),F1300) [The previous formatting you applied - fontbold red? - should still be there Otherwise, just format to taste] Click OK The CF formula will ensure that all your column headings ("24hr. total") in col F, which are *not* numbers, will remain "unformatted" -- Rgds Max xl 97 --- Please respond, in newsgroup xdemechanik atyahoodotcom ---- "steve" wrote in message link.net... Hello, Thanks for your reply. I just wanted to say that it works but there is one minor problem which I forgot to explain. In my example: date time day evening night 24hr. total (no red) 3/15/04 8:00 100 3/15/04 12:00 300 3/15/04 4:00 600 1000 (red) date time day evening night 24hr. total (no red) 3/16/04 8:00 100 3/16/04 12:00 300 3/16/04 4:00 600 1000 (red) I don't want any of the column headings (24hrs. total to be included in red). I only want the numbers. DDM wrote in message ... Steve, highlight Column F (click the column heading at the top of the worksheet), then pull down the Format menu and select Conditional Formatting. Your condition is "Cell value is greater than 300." Click the Format button, select the Font tab, select red for the font color, then click OK, and OK again. -- DDM "DDM's Microsoft Office Tips and Tricks" Visit us at www.ddmcomputing.com "steve" wrote in message link.net... Hello, What I am trying to do is lets say I have a bunch of numbers in column F (for illustration purposes I will only show one number) and what I would like if any of the numbers in column F are greater than 300 I want it to change the color of that number to red automatical (See example) date time day evening night 24hr. Totals 3/12/04 8:00 100 12:00 300 4:00 600 1000 (change to red) I have no problem with changing the number to red manually but it gets to be a pain in the neck when the spreadsheet gets really long. Any help to this would be appreciated. |
#5
|
|||
|
|||
Conditioning formating a data value to change to a color
Comments on removal of C.F. from rows (Max's suggestion A):
One of the problems of C.F. is knowing the extent of the C.F. and trying to guess what cells were originally selected when the C.F. was created. If you choose entire columns or the entire worksheet then you can correctly guess the extent. Making changes to C.F.: I don't know of any way to select a cell and extend the selection to other cells that have the same C.F. If this were possible it would be a lot easier to work with C.F. If you removed the conditional formatting for cells at the top you will have to reselect the original C.F. and then move with the arrow keys to the proper cell one row down, modify the formula or formatting and then remove the C.F. from your header row again. One way to avoid that is to include additional code in the C.F. for exclusion or make sure that the formulas only apply to certain kinds of data in order to reduce the scope within the formula. Changes to Formulas: So my preference would be to change the formulas as in Max's second suggestion (B). My page on Conditional Formatting is: http://www.mvps.org/dmcritchie/excel/condfmt.htm --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Max" wrote in message ... 2 ways you can try: A. Remove the conditional formatting [CF] for the headers Select any empty cell (which is without the CF) *Double-click* on the Format Painter button ("brush" icon) Now just click on each of the header cells in col F in turn to "paint" over and remove the CF When done, press Esc to revert the cursor to normal B. Change the conditional formatting [CF] to one using "formulas" instead Select col F Click Format Conditional Formatting Under "Condition 1", make the settings: Formula Is | =AND(ISNUMBER(F1),F1300) [The previous formatting you applied - fontbold red? - should still be there Otherwise, just format to taste] Click OK The CF formula will ensure that all your column headings ("24hr. total") in col F, which are *not* numbers, will remain "unformatted" -- Rgds Max xl 97 --- Please respond, in newsgroup xdemechanik atyahoodotcom ---- "steve" wrote in message link.net... Hello, Thanks for your reply. I just wanted to say that it works but there is one minor problem which I forgot to explain. In my example: date time day evening night 24hr. total (no red) 3/15/04 8:00 100 3/15/04 12:00 300 3/15/04 4:00 600 1000 (red) date time day evening night 24hr. total (no red) 3/16/04 8:00 100 3/16/04 12:00 300 3/16/04 4:00 600 1000 (red) I don't want any of the column headings (24hrs. total to be included in red). I only want the numbers. DDM wrote in message ... Steve, highlight Column F (click the column heading at the top of the worksheet), then pull down the Format menu and select Conditional Formatting. Your condition is "Cell value is greater than 300." Click the Format button, select the Font tab, select red for the font color, then click OK, and OK again. -- DDM "DDM's Microsoft Office Tips and Tricks" Visit us at www.ddmcomputing.com "steve" wrote in message link.net... Hello, What I am trying to do is lets say I have a bunch of numbers in column F (for illustration purposes I will only show one number) and what I would like if any of the numbers in column F are greater than 300 I want it to change the color of that number to red automatical (See example) date time day evening night 24hr. Totals 3/12/04 8:00 100 12:00 300 4:00 600 1000 (change to red) I have no problem with changing the number to red manually but it gets to be a pain in the neck when the spreadsheet gets really long. Any help to this would be appreciated. |
#6
|
|||
|
|||
Conditioning formating a data value to change to a color
Thanks for insights, Dave !
-- Rgds Max xl 97 --- Please respond, in newsgroup xdemechanik atyahoodotcom ---- |
Thread Tools | |
Display Modes | |
|
|