Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can I use 4 conditonal foamts in a column?
The conditional formatting in Excel 2003 is limited to 3 conditions. I need
four: Bewteen: 13 - 16 = Red pattern 9 - 12 = Green pattern 5 - 8 = Yellow pattern 1 - 4 = Gray pattern I thought I ran across a web page at one point where this was talked about and that it is possible. Does anyone have an idea as to how to do this, or where I can find a sample? Mike |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can I use 4 conditonal foamts in a column?
If you truly only need 4, then just use the default format as your 4th.
Instead of setting 1-4 to be Grey, just make the cells grey by default, then use CF for the other conditions. If, however, you actually need 5 formats (e.g. no format), then this wouldn't work. You'd need to use VB Code. Set up an WorksheetChange Event, test the value of each cell, then apply the formats from there. Or, upgrade to Excel 2007. HTH Elkar "watermt" wrote: The conditional formatting in Excel 2003 is limited to 3 conditions. I need four: Bewteen: 13 - 16 = Red pattern 9 - 12 = Green pattern 5 - 8 = Yellow pattern 1 - 4 = Gray pattern I thought I ran across a web page at one point where this was talked about and that it is possible. Does anyone have an idea as to how to do this, or where I can find a sample? Mike |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can I use 4 conditonal foamts in a column?
Hi, as you have your default pattern + 3 conditional formats, you have the 4 formats you need. watermt;286353 Wrote: The conditional formatting in Excel 2003 is limited to 3 conditions. I need four: Bewteen: 13 - 16 = Red pattern 9 - 12 = Green pattern 5 - 8 = Yellow pattern 1 - 4 = Gray pattern I thought I ran across a web page at one point where this was talked about and that it is possible. Does anyone have an idea as to how to do this, or where I can find a sample? Mike -- Pecoflyer Cheers - *'Membership is free' (http://www.thecodecage.com)* & allows file upload -faster and better answers *Adding your XL version* to your post helps finding solution faster ------------------------------------------------------------------------ Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=79991 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can I use 4 conditonal foamts in a column?
Hi,
Right click your sheet tab, voew code and paste this in. Change target to column (currently 1) for column A to your column Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column < 1 Or Target.Cells.Count 1 _ Or IsEmpty(Target) Then Exit Sub Select Case Target.Value Case 1 To 4 icolor = 15 Case 5 To 8 icolor = 6 Case 9 To 12 icolor = 4 Case 13 To 16 icolor = 3 Case Else icolor = xlNone End Select Target.Interior.ColorIndex = icolor End Sub Mike "watermt" wrote: The conditional formatting in Excel 2003 is limited to 3 conditions. I need four: Bewteen: 13 - 16 = Red pattern 9 - 12 = Green pattern 5 - 8 = Yellow pattern 1 - 4 = Gray pattern I thought I ran across a web page at one point where this was talked about and that it is possible. Does anyone have an idea as to how to do this, or where I can find a sample? Mike |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can I use 4 conditonal foamts in a column?
Use conditional formatting for the green, yellow, and gray conditions.
To get the 4th, go to format cell, custom format, and input: [Black][16];[Red][=14];[Black] note that if you don't want a default color of black for anything else, other possible color options a [Black] [Blue] [Cyan] [Green] [Magenta] [Red] [White] [Yellow] -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "watermt" wrote: The conditional formatting in Excel 2003 is limited to 3 conditions. I need four: Bewteen: 13 - 16 = Red pattern 9 - 12 = Green pattern 5 - 8 = Yellow pattern 1 - 4 = Gray pattern I thought I ran across a web page at one point where this was talked about and that it is possible. Does anyone have an idea as to how to do this, or where I can find a sample? Mike |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can I use 4 conditonal foamts in a column?
Mike H,
I'm not very good at using VB and am not exactly clear on what you mean by "change target to column (currently 1) for column A to your column". My column is F (cells F3 through F20). I tried to replace the number 1 and the word target in the code with F; then tried F3:F20, and also tried the number 6 (thinking 1 =a, 2=b, 3=c, 4=d, 5=e, 6=f). But was unsuccessful. Could you clarify for me exactly where in the code I need to make a change and what do I replace what with? By the way, I have initially set the cellss font color to white so that the #N/A does not display when no selections have been made in cells that affect the color change. I hope this is not a problem with your fix? But, I didn't want staff freakin' out about seeing the #N/A displayed as a default symbol in the blank cells. Sorry about my lack of expertise with Excel and VB, Mike "Mike H" wrote: Hi, Right click your sheet tab, voew code and paste this in. Change target to column (currently 1) for column A to your column Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column < 1 Or Target.Cells.Count 1 _ Or IsEmpty(Target) Then Exit Sub Select Case Target.Value Case 1 To 4 icolor = 15 Case 5 To 8 icolor = 6 Case 9 To 12 icolor = 4 Case 13 To 16 icolor = 3 Case Else icolor = xlNone End Select Target.Interior.ColorIndex = icolor End Sub Mike "watermt" wrote: The conditional formatting in Excel 2003 is limited to 3 conditions. I need four: Bewteen: 13 - 16 = Red pattern 9 - 12 = Green pattern 5 - 8 = Yellow pattern 1 - 4 = Gray pattern I thought I ran across a web page at one point where this was talked about and that it is possible. Does anyone have an idea as to how to do this, or where I can find a sample? Mike |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can I use 4 conditonal foamts in a column?
Luke,
I tried your recommendation, but when I tried to input the [Black][16];[Red][=14];[Black] into the Foramet Cells - Number - Custom area it gave me an error message. Something about not being able to do this and to try using one of the provided number formats? Is there something that I'm missing here? Mike "Luke M" wrote: Use conditional formatting for the green, yellow, and gray conditions. To get the 4th, go to format cell, custom format, and input: [Black][16];[Red][=14];[Black] note that if you don't want a default color of black for anything else, other possible color options a [Black] [Blue] [Cyan] [Green] [Magenta] [Red] [White] [Yellow] -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "watermt" wrote: The conditional formatting in Excel 2003 is limited to 3 conditions. I need four: Bewteen: 13 - 16 = Red pattern 9 - 12 = Green pattern 5 - 8 = Yellow pattern 1 - 4 = Gray pattern I thought I ran across a web page at one point where this was talked about and that it is possible. Does anyone have an idea as to how to do this, or where I can find a sample? Mike |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can I use 4 conditonal foamts in a column?
Thanks for the info on the Code Cage, just registered, waiting for my email
reply from them. One point I may have failed to mention about my Excel 2003 issue is that I'm running an HLOOKUP formula to display the number in the cells I want to highlight with red, green, yellow and gray. Each of the number cells currently display #N/A and the only way I was able to get rid of that from being displayed was to format the cells for white text. So, if I use white as a background color the numbers do not display. By the way, my choice of cell highlighting is not something I just conjured up, it's an internal compliance issue that I must follow. Is there a better way to get rid of the #N/A? My HLOOKUP is working as i intended it to do so!? Mike "Pecoflyer" wrote: Hi, as you have your default pattern + 3 conditional formats, you have the 4 formats you need. watermt;286353 Wrote: The conditional formatting in Excel 2003 is limited to 3 conditions. I need four: Bewteen: 13 - 16 = Red pattern 9 - 12 = Green pattern 5 - 8 = Yellow pattern 1 - 4 = Gray pattern I thought I ran across a web page at one point where this was talked about and that it is possible. Does anyone have an idea as to how to do this, or where I can find a sample? Mike -- Pecoflyer Cheers - *'Membership is free' (http://www.thecodecage.com)* & allows file upload -faster and better answers *Adding your XL version* to your post helps finding solution faster ------------------------------------------------------------------------ Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=79991 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can I use 4 conditonal foamts in a column?
Hi,
Try this [Black][16]General;[Red][=14]General; -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "watermt" wrote in message ... Luke, I tried your recommendation, but when I tried to input the [Black][16];[Red][=14];[Black] into the Foramet Cells - Number - Custom area it gave me an error message. Something about not being able to do this and to try using one of the provided number formats? Is there something that I'm missing here? Mike "Luke M" wrote: Use conditional formatting for the green, yellow, and gray conditions. To get the 4th, go to format cell, custom format, and input: [Black][16];[Red][=14];[Black] note that if you don't want a default color of black for anything else, other possible color options a [Black] [Blue] [Cyan] [Green] [Magenta] [Red] [White] [Yellow] -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "watermt" wrote: The conditional formatting in Excel 2003 is limited to 3 conditions. I need four: Bewteen: 13 - 16 = Red pattern 9 - 12 = Green pattern 5 - 8 = Yellow pattern 1 - 4 = Gray pattern I thought I ran across a web page at one point where this was talked about and that it is possible. Does anyone have an idea as to how to do this, or where I can find a sample? Mike |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditonal Formatting | Excel Discussion (Misc queries) | |||
Conditonal lay-out | Excel Worksheet Functions | |||
conditonal formatting in VB | Excel Worksheet Functions | |||
5 or more Conditonal formats | Excel Discussion (Misc queries) | |||
Conditonal formatting | Excel Discussion (Misc queries) |