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 |
#11
|
|||
|
|||
Conditional Formatting - Getting pass the 3 condition limit
What Excel version?
-- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Hadidas" wrote in message ... I had a similar problem and I followed your advice for this, it worked wonderfully. I am having one problem though. My spreadsheet has a column that uses a Validation List, it is possible to choose from 6 choices. G, Y, R, N/A, No Criteria, and a blank cell. I used the VBA code you suggested to color G green, Y yellow, R red, N/A blue, No Criteria white, and the blank cell blank. The problem is that since the G, Y, R, etc are chosen from a list, the cell does not change color unless I actually enter the cell (F2), so if the cell is red, and I choose G (from the list), it stays red. Is there a way to refresh the screen in a way, so that the formatting works real time? I even tried making another cell equal that cell, but the same thing happens. Thanks. "Gord Dibben" wrote: Cynthia Not Frank but...... The third and fourth line are all one line. Place a space _ after the word "Nothing" If Intersect(Target, Me.Range("C1:C300")) Is Nothing _ Then Exit Sub I would also stick an Option Compare Text above the Sub to make the entries case-insensitive. Option Compare Text Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Me.Range("C1:C300")) Is Nothing _ Then Exit Sub On Error GoTo CleanUp Application.EnableEvents = False With Target Select Case .Value Case "Red": .Interior.ColorIndex = 3 Case "Blue": .Interior.ColorIndex = 10 Case "Green": .Interior.ColorIndex = 4 Case "Yellow": .Interior.ColorIndex = 6 Case "Brown": .Interior.ColorIndex = 9 Case "Black": .Interior.ColorIndex = 1 End Select End With CleanUp: Application.EnableEvents = True End Sub Gord Dibben Excel MVP On Tue, 31 Aug 2004 11:57:02 -0700, "Cynthia" wrote: Frank you've been so helpful I'm hoping you can give me one more hint. Here is the script I have based on your answer below. In my spreadsheet the column I want to change colors is C: so I changed the range below from what you had. In my editor I get a compile error. The 3rd & 4th lines below (If intersect....then sub) are highlighted in red. Not sure of what I should do here to get this to work. What I'm interested in is having the whole column C: (not just a range) be formatted in this manner. Can you help one more time. Thanks, Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Me.Range("C1:C300")) Is Nothing Then Exit Sub On Error GoTo CleanUp Application.EnableEvents = False With Target Select Case .Value Case "Red": .Interior.ColorIndex = 3 Case "Blue": .Interior.ColorIndex = 10 Case "Green": .Interior.ColorIndex = 4 Case "Yellow": .Interior.ColorIndex = 6 Case "Brown": .Interior.ColorIndex = 9 Case "Black": .Interior.ColorIndex = 1 End Select End With CleanUp: Application.EnableEvents = True End Sub "Frank Kabel" wrote: Hi The following will color the entry in cell A1:A100 based on its value: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Me.Range("A1:A100")) Is Nothing Then Exit Sub On Error GoTo CleanUp Application.EnableEvents = False With Target Select Case .Value Case "Red": .Interior.ColorIndex = 3 Case "Blue": .Interior.ColorIndex = 10 'etc. End Select End With CleanUp: Application.EnableEvents = True End Sub For more about event procedures see: http://www.cpearson.com/excel/events.htm -- Regards Frank Kabel Frankfurt, Germany "Cynthia" schrieb im Newsbeitrag ... Frank, I don't know VBA programming. I'm new to the user discussion group. Is there an area with scripts that I could search? "Frank Kabel" wrote: Hi more conditions are only available if you use VBA. Would this be a way for you?. -- Regards Frank Kabel Frankfurt, Germany "Cynthia" schrieb im Newsbeitrag news I have a spreadsheet where I have set a data validation for the colum to select from a list. The list has names of colors. (Red, yellow, etc) A total of 6 colors. I then set a conditional format for the column to change the background of the cell to the selected color. (The word "red" displays in cell and background color of cell is red). Excel limits the number of conditions to 3. I have 6 color condtions (red, yellow, green, blue, brown, black). How do I get around the 3 limit condition where I can set all 6 colors to change when the text is selected from the drop down. |
#12
|
|||
|
|||
Conditional Formatting - Getting pass the 3 condition limit
it's excel 2000
"Bob Phillips" wrote: What Excel version? -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Hadidas" wrote in message ... I had a similar problem and I followed your advice for this, it worked wonderfully. I am having one problem though. My spreadsheet has a column that uses a Validation List, it is possible to choose from 6 choices. G, Y, R, N/A, No Criteria, and a blank cell. I used the VBA code you suggested to color G green, Y yellow, R red, N/A blue, No Criteria white, and the blank cell blank. The problem is that since the G, Y, R, etc are chosen from a list, the cell does not change color unless I actually enter the cell (F2), so if the cell is red, and I choose G (from the list), it stays red. Is there a way to refresh the screen in a way, so that the formatting works real time? I even tried making another cell equal that cell, but the same thing happens. Thanks. "Gord Dibben" wrote: Cynthia Not Frank but...... The third and fourth line are all one line. Place a space _ after the word "Nothing" If Intersect(Target, Me.Range("C1:C300")) Is Nothing _ Then Exit Sub I would also stick an Option Compare Text above the Sub to make the entries case-insensitive. Option Compare Text Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Me.Range("C1:C300")) Is Nothing _ Then Exit Sub On Error GoTo CleanUp Application.EnableEvents = False With Target Select Case .Value Case "Red": .Interior.ColorIndex = 3 Case "Blue": .Interior.ColorIndex = 10 Case "Green": .Interior.ColorIndex = 4 Case "Yellow": .Interior.ColorIndex = 6 Case "Brown": .Interior.ColorIndex = 9 Case "Black": .Interior.ColorIndex = 1 End Select End With CleanUp: Application.EnableEvents = True End Sub Gord Dibben Excel MVP On Tue, 31 Aug 2004 11:57:02 -0700, "Cynthia" wrote: Frank you've been so helpful I'm hoping you can give me one more hint. Here is the script I have based on your answer below. In my spreadsheet the column I want to change colors is C: so I changed the range below from what you had. In my editor I get a compile error. The 3rd & 4th lines below (If intersect....then sub) are highlighted in red. Not sure of what I should do here to get this to work. What I'm interested in is having the whole column C: (not just a range) be formatted in this manner. Can you help one more time. Thanks, Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Me.Range("C1:C300")) Is Nothing Then Exit Sub On Error GoTo CleanUp Application.EnableEvents = False With Target Select Case .Value Case "Red": .Interior.ColorIndex = 3 Case "Blue": .Interior.ColorIndex = 10 Case "Green": .Interior.ColorIndex = 4 Case "Yellow": .Interior.ColorIndex = 6 Case "Brown": .Interior.ColorIndex = 9 Case "Black": .Interior.ColorIndex = 1 End Select End With CleanUp: Application.EnableEvents = True End Sub "Frank Kabel" wrote: Hi The following will color the entry in cell A1:A100 based on its value: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Me.Range("A1:A100")) Is Nothing Then Exit Sub On Error GoTo CleanUp Application.EnableEvents = False With Target Select Case .Value Case "Red": .Interior.ColorIndex = 3 Case "Blue": .Interior.ColorIndex = 10 'etc. End Select End With CleanUp: Application.EnableEvents = True End Sub For more about event procedures see: http://www.cpearson.com/excel/events.htm -- Regards Frank Kabel Frankfurt, Germany "Cynthia" schrieb im Newsbeitrag ... Frank, I don't know VBA programming. I'm new to the user discussion group. Is there an area with scripts that I could search? "Frank Kabel" wrote: Hi more conditions are only available if you use VBA. Would this be a way for you?. -- Regards Frank Kabel Frankfurt, Germany "Cynthia" schrieb im Newsbeitrag news I have a spreadsheet where I have set a data validation for the colum to select from a list. The list has names of colors. (Red, yellow, etc) A total of 6 colors. I then set a conditional format for the column to change the background of the cell to the selected color. (The word "red" displays in cell and background color of cell is red). Excel limits the number of conditions to 3. I have 6 color condtions (red, yellow, green, blue, brown, black). How do I get around the 3 limit condition where I can set all 6 colors to change when the text is selected from the drop down. |
#13
|
|||
|
|||
Conditional Formatting - Getting pass the 3 condition limit
Could have explained it if was Excel 97, but not 2000. Sorry.
-- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Hadidas" wrote in message ... it's excel 2000 "Bob Phillips" wrote: What Excel version? -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Hadidas" wrote in message ... I had a similar problem and I followed your advice for this, it worked wonderfully. I am having one problem though. My spreadsheet has a column that uses a Validation List, it is possible to choose from 6 choices. G, Y, R, N/A, No Criteria, and a blank cell. I used the VBA code you suggested to color G green, Y yellow, R red, N/A blue, No Criteria white, and the blank cell blank. The problem is that since the G, Y, R, etc are chosen from a list, the cell does not change color unless I actually enter the cell (F2), so if the cell is red, and I choose G (from the list), it stays red. Is there a way to refresh the screen in a way, so that the formatting works real time? I even tried making another cell equal that cell, but the same thing happens. Thanks. "Gord Dibben" wrote: Cynthia Not Frank but...... The third and fourth line are all one line. Place a space _ after the word "Nothing" If Intersect(Target, Me.Range("C1:C300")) Is Nothing _ Then Exit Sub I would also stick an Option Compare Text above the Sub to make the entries case-insensitive. Option Compare Text Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Me.Range("C1:C300")) Is Nothing _ Then Exit Sub On Error GoTo CleanUp Application.EnableEvents = False With Target Select Case .Value Case "Red": .Interior.ColorIndex = 3 Case "Blue": .Interior.ColorIndex = 10 Case "Green": .Interior.ColorIndex = 4 Case "Yellow": .Interior.ColorIndex = 6 Case "Brown": .Interior.ColorIndex = 9 Case "Black": .Interior.ColorIndex = 1 End Select End With CleanUp: Application.EnableEvents = True End Sub Gord Dibben Excel MVP On Tue, 31 Aug 2004 11:57:02 -0700, "Cynthia" wrote: Frank you've been so helpful I'm hoping you can give me one more hint. Here is the script I have based on your answer below. In my spreadsheet the column I want to change colors is C: so I changed the range below from what you had. In my editor I get a compile error. The 3rd & 4th lines below (If intersect....then sub) are highlighted in red. Not sure of what I should do here to get this to work. What I'm interested in is having the whole column C: (not just a range) be formatted in this manner. Can you help one more time. Thanks, Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Me.Range("C1:C300")) Is Nothing Then Exit Sub On Error GoTo CleanUp Application.EnableEvents = False With Target Select Case .Value Case "Red": .Interior.ColorIndex = 3 Case "Blue": .Interior.ColorIndex = 10 Case "Green": .Interior.ColorIndex = 4 Case "Yellow": .Interior.ColorIndex = 6 Case "Brown": .Interior.ColorIndex = 9 Case "Black": .Interior.ColorIndex = 1 End Select End With CleanUp: Application.EnableEvents = True End Sub "Frank Kabel" wrote: Hi The following will color the entry in cell A1:A100 based on its value: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Me.Range("A1:A100")) Is Nothing Then Exit Sub On Error GoTo CleanUp Application.EnableEvents = False With Target Select Case .Value Case "Red": .Interior.ColorIndex = 3 Case "Blue": .Interior.ColorIndex = 10 'etc. End Select End With CleanUp: Application.EnableEvents = True End Sub For more about event procedures see: http://www.cpearson.com/excel/events.htm -- Regards Frank Kabel Frankfurt, Germany "Cynthia" schrieb im Newsbeitrag ... Frank, I don't know VBA programming. I'm new to the user discussion group. Is there an area with scripts that I could search? "Frank Kabel" wrote: Hi more conditions are only available if you use VBA. Would this be a way for you?. -- Regards Frank Kabel Frankfurt, Germany "Cynthia" schrieb im Newsbeitrag news I have a spreadsheet where I have set a data validation for the colum to select from a list. The list has names of colors. (Red, yellow, etc) A total of 6 colors. I then set a conditional format for the column to change the background of the cell to the selected color. (The word "red" displays in cell and background color of cell is red). Excel limits the number of conditions to 3. I have 6 color condtions (red, yellow, green, blue, brown, black). How do I get around the 3 limit condition where I can set all 6 colors to change when the text is selected from the drop down. |
#14
|
|||
|
|||
Conditional Formatting - Getting pass the 3 condition limit
I also need this help and am not sure how to access the VBE in the system, in
addition I am referencing a list of 15 numbers each corresponds to a different background and need the background to fill an entire row selection based on the number in the given column. This is for color tracking of application data as the application works its way through the process each number corresponds to a step in the process and I want the row of data to change automatically when I change the number on the row. Thank you. -- Judy Rose Cohen "Cynthia" wrote: Frank you've been so helpful I'm hoping you can give me one more hint. Here is the script I have based on your answer below. In my spreadsheet the column I want to change colors is C: so I changed the range below from what you had. In my editor I get a compile error. The 3rd & 4th lines below (If intersect....then sub) are highlighted in red. Not sure of what I should do here to get this to work. What I'm interested in is having the whole column C: (not just a range) be formatted in this manner. Can you help one more time. Thanks, Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Me.Range("C1:C300")) Is Nothing Then Exit Sub On Error GoTo CleanUp Application.EnableEvents = False With Target Select Case .Value Case "Red": .Interior.ColorIndex = 3 Case "Blue": .Interior.ColorIndex = 10 Case "Green": .Interior.ColorIndex = 4 Case "Yellow": .Interior.ColorIndex = 6 Case "Brown": .Interior.ColorIndex = 9 Case "Black": .Interior.ColorIndex = 1 End Select End With CleanUp: Application.EnableEvents = True End Sub "Frank Kabel" wrote: Hi The following will color the entry in cell A1:A100 based on its value: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Me.Range("A1:A100")) Is Nothing Then Exit Sub On Error GoTo CleanUp Application.EnableEvents = False With Target Select Case .Value Case "Red": .Interior.ColorIndex = 3 Case "Blue": .Interior.ColorIndex = 10 'etc. End Select End With CleanUp: Application.EnableEvents = True End Sub For more about event procedures see: http://www.cpearson.com/excel/events.htm -- Regards Frank Kabel Frankfurt, Germany "Cynthia" schrieb im Newsbeitrag ... Frank, I don't know VBA programming. I'm new to the user discussion group. Is there an area with scripts that I could search? "Frank Kabel" wrote: Hi more conditions are only available if you use VBA. Would this be a way for you?. -- Regards Frank Kabel Frankfurt, Germany "Cynthia" schrieb im Newsbeitrag news I have a spreadsheet where I have set a data validation for the colum to select from a list. The list has names of colors. (Red, yellow, etc) A total of 6 colors. I then set a conditional format for the column to change the background of the cell to the selected color. (The word "red" displays in cell and background color of cell is red). Excel limits the number of conditions to 3. I have 6 color condtions (red, yellow, green, blue, brown, black). How do I get around the 3 limit condition where I can set all 6 colors to change when the text is selected from the drop down. |
#15
|
|||
|
|||
Conditional Formatting - Getting pass the 3 condition limit
Right-click on the worksheet tab and "View Code"
Copy/paste this code into that module. Private Sub Worksheet_Change(ByVal Target As Range) Set r = Range("A:A") 'adjust to suit your range If Intersect(Target, r) Is Nothing Then Exit Sub End If vals = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10) 'your 15 numbers will go here nums = Array(8, 9, 6, 3, 7, 4, 20, 10, 16, 15) 'you need 15 colorindex numbers For Each rr In r icolor = 0 For i = LBound(vals) To UBound(vals) If rr.Value = vals(i) Then icolor = nums(i) End If Next If icolor 0 Then rr.EntireRow.Interior.ColorIndex = icolor End If Next End Sub This code has been tested with 10 numbers in the vals array The nums array is the colorindex numbers from the Excel color palette You will have to adjust and edit to suit. For a list of the colorindex numbers see David McRitchie's site http://www.mvps.org/dmcritchie/excel/colors.htm When editing is complete, Alt + q to return to the Excel window. As always, do all of this on a copy of the worksheet or workbook. Gord Dibben MS Excel MVP On Wed, 14 May 2008 09:30:02 -0700, Judy Rose wrote: I also need this help and am not sure how to access the VBE in the system, in addition I am referencing a list of 15 numbers each corresponds to a different background and need the background to fill an entire row selection based on the number in the given column. This is for color tracking of application data as the application works its way through the process each number corresponds to a step in the process and I want the row of data to change automatically when I change the number on the row. Thank you. -- Judy Rose Cohen |
#16
|
|||
|
|||
Conditional Formatting - Getting pass the 3 condition limit
Thank you. Only issue I had was coming up with enough colors different
enough to cover all my categories. Is there a way I could specify if a certain color fill is used to also use a set pattern or font color? I understood where your programming came from but wasn't sure where I could indicate if icolor= (certain value) Then font for entire row = Bold/Italics, etc.....How to get the program to acknowledge the fact that for a given color for a row I need the change in font so it will be more visible. If this is possible would you let me know. Again, thank you for the help, it worked really well. -- Judy Rose Cohen "Gord Dibben" wrote: Right-click on the worksheet tab and "View Code" Copy/paste this code into that module. Private Sub Worksheet_Change(ByVal Target As Range) Set r = Range("A:A") 'adjust to suit your range If Intersect(Target, r) Is Nothing Then Exit Sub End If vals = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10) 'your 15 numbers will go here nums = Array(8, 9, 6, 3, 7, 4, 20, 10, 16, 15) 'you need 15 colorindex numbers For Each rr In r icolor = 0 For i = LBound(vals) To UBound(vals) If rr.Value = vals(i) Then icolor = nums(i) End If Next If icolor 0 Then rr.EntireRow.Interior.ColorIndex = icolor End If Next End Sub This code has been tested with 10 numbers in the vals array The nums array is the colorindex numbers from the Excel color palette You will have to adjust and edit to suit. For a list of the colorindex numbers see David McRitchie's site http://www.mvps.org/dmcritchie/excel/colors.htm When editing is complete, Alt + q to return to the Excel window. As always, do all of this on a copy of the worksheet or workbook. Gord Dibben MS Excel MVP On Wed, 14 May 2008 09:30:02 -0700, Judy Rose wrote: I also need this help and am not sure how to access the VBE in the system, in addition I am referencing a list of 15 numbers each corresponds to a different background and need the background to fill an entire row selection based on the number in the given column. This is for color tracking of application data as the application works its way through the process each number corresponds to a step in the process and I want the row of data to change automatically when I change the number on the row. Thank you. -- Judy Rose Cohen |
#17
|
|||
|
|||
Conditional Formatting - Getting pass the 3 condition limit
I would go with darker fill colors then color the font white.
Make changes as such............... Next If icolor 0 Then With rr .Interior.ColorIndex = icolor .Font.ColorIndex = 2 .Font.Bold = True End With End If Next BTW.........Your original post stated 15 numbers. Excel has 56 colors in the color palette. Gord On Fri, 16 May 2008 11:13:00 -0700, Judy Rose wrote: Thank you. Only issue I had was coming up with enough colors different enough to cover all my categories. Is there a way I could specify if a certain color fill is used to also use a set pattern or font color? I understood where your programming came from but wasn't sure where I could indicate if icolor= (certain value) Then font for entire row = Bold/Italics, etc.....How to get the program to acknowledge the fact that for a given color for a row I need the change in font so it will be more visible. If this is possible would you let me know. Again, thank you for the help, it worked really well. |
#18
|
|||
|
|||
Conditional Formatting - Getting pass the 3 condition limit
Originally I did only have 15, then realized I forgot one type of application
condition, where do I insert the code for the font, is it a modification of the following; (sorry have a mental block when it comes to programming, you'd think after 20+ years i wouldn't, but I do....) Next If icolor 0 Then rr.EntireRow.Interior.ColorIndex = icolor End If Next End Sub Again thank you for your assistance -- Judy Rose Cohen "Gord Dibben" wrote: I would go with darker fill colors then color the font white. Make changes as such............... Next If icolor 0 Then With rr .Interior.ColorIndex = icolor .Font.ColorIndex = 2 .Font.Bold = True End With End If Next BTW.........Your original post stated 15 numbers. Excel has 56 colors in the color palette. Gord On Fri, 16 May 2008 11:13:00 -0700, Judy Rose wrote: Thank you. Only issue I had was coming up with enough colors different enough to cover all my categories. Is there a way I could specify if a certain color fill is used to also use a set pattern or font color? I understood where your programming came from but wasn't sure where I could indicate if icolor= (certain value) Then font for entire row = Bold/Italics, etc.....How to get the program to acknowledge the fact that for a given color for a row I need the change in font so it will be more visible. If this is possible would you let me know. Again, thank you for the help, it worked really well. |
#19
|
|||
|
|||
Conditional Formatting - Getting pass the 3 condition limit
Just reread the code and figured out what you were saying, so I gather than
that I cannot do another sub of the original sub to specify not only to change the color to a specific for each icolor, but with certain icolor to change the font on only those particular ones that are too dark to having the font go white. I just had one that I used in the original that the color was too dark and it was only that particular color I wanted the change in the font. In my previous worksheets I had done the change of the rows to colors manually each time and so I had certain patterns used with certain colors to ensure they had visible fonts, however didn't see a way to do a case by case so that for each value the fill would be the base color, plus a given pattern, plus a given font, I saw something with specification for cases, but with the array the formula worked better for my needs. -- Judy Rose Cohen "Gord Dibben" wrote: I would go with darker fill colors then color the font white. Make changes as such............... Next If icolor 0 Then With rr .Interior.ColorIndex = icolor .Font.ColorIndex = 2 .Font.Bold = True End With End If Next BTW.........Your original post stated 15 numbers. Excel has 56 colors in the color palette. Gord On Fri, 16 May 2008 11:13:00 -0700, Judy Rose wrote: Thank you. Only issue I had was coming up with enough colors different enough to cover all my categories. Is there a way I could specify if a certain color fill is used to also use a set pattern or font color? I understood where your programming came from but wasn't sure where I could indicate if icolor= (certain value) Then font for entire row = Bold/Italics, etc.....How to get the program to acknowledge the fact that for a given color for a row I need the change in font so it will be more visible. If this is possible would you let me know. Again, thank you for the help, it worked really well. |
#20
|
|||
|
|||
Conditional Formatting - Getting pass the 3 condition limit
If you want to change the font color to white as suggested, just alter the code
as I posted in my last reply. If you want to have each case a different colored font. Private Sub Worksheet_Change(ByVal Target As Range) Set r = Range("A1:A50") If Intersect(Target, r) Is Nothing Then Exit Sub End If vals = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10) nums = Array(8, 9, 6, 3, 7, 4, 20, 10, 16, 15) fnts = Array(2, 3, 4, 5, 6, 7, 8, 9, 10, 11) For Each rr In r icolor = 0 jcolor = 0 For i = LBound(vals) To UBound(vals) If rr.Value = vals(i) Then icolor = nums(i) jcolor = fnts(i) End If Next If icolor 0 And jcolor 0 Then rr.Interior.ColorIndex = icolor rr.Font.ColorIndex = jcolor End If Next End Sub Gord On Tue, 20 May 2008 07:26:00 -0700, Judy Rose wrote: Originally I did only have 15, then realized I forgot one type of application condition, where do I insert the code for the font, is it a modification of the following; (sorry have a mental block when it comes to programming, you'd think after 20+ years i wouldn't, but I do....) Next If icolor 0 Then rr.EntireRow.Interior.ColorIndex = icolor End If Next End Sub Again thank you for your assistance |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Pivots and keeping conditional formatting | Jenn | General Discussion | 1 | August 11th, 2004 10:35 PM |
Copy Conditional Formatting | Bianca | Worksheet Functions | 2 | August 9th, 2004 01:47 PM |
Conditional Formatting And Running Sums | Michael | Setting Up & Running Reports | 2 | May 24th, 2004 07:16 AM |
Conditional Formatting Questions | Andy B | Worksheet Functions | 1 | May 12th, 2004 02:48 PM |
Conditional Formatting on a cell that has an IF function | DDM | Worksheet Functions | 0 | May 11th, 2004 05:13 PM |