Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting
Excel provides only 3 levels of conditional foramtting.
Using Formatconditional formatting I need to have more than three. Please advise as to how I can do this. I do not know VB script. Would greatly appreciat if you can send a sample with brief. Thanks Aravind:confused -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting
If your data is numeric, and you need no more than six formats, see
http://www.mcgimpsey.com/excel/conditional6.html If not, then you'll need VBA. Since you didn't provide any details, it's hard to recommend a method. Perhaps you can find a useful one by searching the archives: http://groups.google.com/advanced_gr...=group:*excel* In article , aravindvin wrote: Excel provides only 3 levels of conditional foramtting. Using Formatconditional formatting I need to have more than three. Please advise as to how I can do this. I do not know VB script. Would greatly appreciat if you can send a sample with brief. Thanks Aravind --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting
Please find attached the dummy excel to view the requirement.
Requirement::: The color of cells should change based on the values in two other cell (importance and rating). Importance: This value determines the number of colored cells Rating: This value determines the color of the cells Pls. let me know how we can do this in VB or conditional formatting. (requires more than 3 conditional formats) Thanks Aravin Attachment filename: worksheet.xls Download attachment: http://www.excelforum.com/attachment.php?postid=65897 -- Message posted from http://www.ExcelForum.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting
Sorry - I don't open unsolicited workbooks (virii, for one thing, and
long experience that people usually attach workbooks because they're too lazy to clearly articulate their request, for another). I also really would rather not respond to those that are unwilling to help themselves by looking in the archives when they've been pointed to them, much less those who simply repeat requirements, rather than explaining why a technique suggested in a previous reply won't work... Perhaps someone else will be more generous. In article , aravindvin wrote: Please find attached the dummy excel to view the requirement. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting
Hi Arvind,
I looked at your workbook, and because you do not give a description in text of what you want, I will have to describe in text what you wanted, but more directed to what this Event Macro will be doing. Bar Graph composed of cells, for a severity ranked by color Your spreadsheet has entry in columns 8 an 9 (H & I) Column H provides a number 0 to 4 (though you white out 5) Column I provides a number 1 to 5 to create a color (colorindex used) Columns 2:6 are colored from the left for a length of what is in Column H, for a color derived from Column I (col 9). ColorIndex values can be found in http://www.mvps.org/dmcritchie/excel/colors.htm 1=Black, 2=White, 3=Red, 6=Yellow, 10=dark Green, 46 Orange Use of Event macros can be found in http://www.mvps.org/dmcritchie/excel/event.htm Install by rightclick on the worksheet tab, view code Rather than using .clear the interior.colorindex is set to white which clears out grid lines, which it appears you did not want to see. Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column < 8 Or Target.Column 9 Then Exit Sub 'H & I If Target.Row < 2 Then Exit Sub Dim R As Long, I As Long R = Target.Row If Not IsNumeric(Cells(R, 8) & Cells(R, 9)) Then Exit Sub I = Application.Choose(Cells(R, 9).Value, 1, 3, 46, 6, 10) Intersect(Rows(Target.Row), Columns("B:F")).Interior.ColorIndex = 2 Range("B" & R & ":" & Cells(R, 1 + _ Cells(R, 8).Value).Address(0, 0)).Interior.ColorIndex = I End Sub By not describing what you want you put an extra burden on anyone who wants or tries to help. By not doing this effort yourself you imply to others that you don't really care enough to describe the problem in plain text so why should anyone try to help you. And I think the lack of using your full name when corresponding also indicates a further disregard to others in the newsgroup, and in your own contribution. If your 38KB file were actually an attachment it would have made your posting over 80K. Fortunately it is not an attachment but is a file on a website. But for anybody to see what the question is you make them look at the file, and the newsgroup archives http://google.com/advanced_group_sea...Excel*&num=100 will not show what the question is so it would not help others with similar questions who were willing to search archives for something close to what they want. Also see http://www.mvps.org/dmcritchie/excel/xlnews.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 "JE McGimpsey" wrote ... Sorry - I don't open unsolicited workbooks (virii, for one thing, and long experience that people usually attach workbooks because they're too lazy to clearly articulate their request, for another). In article wrote: Please find attached the dummy excel to view the requirement. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting
David,
I'm new to this bulletin board and don't know the most effective way to get a response to a post. I've posted this question somewhere on the bulletin board but after reading a number of your responses, I thought you might be able to help me. Here's my problem: I'd like to format cells in Excel for 10 different text conditions. At the simple end I can format a cell by saying Cell Value is Equal To ="John". I can do that for 3 different names. I can even trick the system by using Cell Value is Between ="John" and ="Mike" and have 3 different formats for 6 conditions. How would I use conditional formatting to for example, turn the cell blue for a John, Mike or Sara entry, green for a Dick or Mary entry and red for a Jane, Bob, Sally or Paul entry? If I have to use a macro to accomplish this, how would I do it? Any help is appreciated. "David McRitchie" wrote: Hi Arvind, I looked at your workbook, and because you do not give a description in text of what you want, I will have to describe in text what you wanted, but more directed to what this Event Macro will be doing. Bar Graph composed of cells, for a severity ranked by color Your spreadsheet has entry in columns 8 an 9 (H & I) Column H provides a number 0 to 4 (though you white out 5) Column I provides a number 1 to 5 to create a color (colorindex used) Columns 2:6 are colored from the left for a length of what is in Column H, for a color derived from Column I (col 9). ColorIndex values can be found in http://www.mvps.org/dmcritchie/excel/colors.htm 1=Black, 2=White, 3=Red, 6=Yellow, 10=dark Green, 46 Orange Use of Event macros can be found in http://www.mvps.org/dmcritchie/excel/event.htm Install by rightclick on the worksheet tab, view code Rather than using .clear the interior.colorindex is set to white which clears out grid lines, which it appears you did not want to see. Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column < 8 Or Target.Column 9 Then Exit Sub 'H & I If Target.Row < 2 Then Exit Sub Dim R As Long, I As Long R = Target.Row If Not IsNumeric(Cells(R, 8) & Cells(R, 9)) Then Exit Sub I = Application.Choose(Cells(R, 9).Value, 1, 3, 46, 6, 10) Intersect(Rows(Target.Row), Columns("B:F")).Interior.ColorIndex = 2 Range("B" & R & ":" & Cells(R, 1 + _ Cells(R, 8).Value).Address(0, 0)).Interior.ColorIndex = I End Sub By not describing what you want you put an extra burden on anyone who wants or tries to help. By not doing this effort yourself you imply to others that you don't really care enough to describe the problem in plain text so why should anyone try to help you. And I think the lack of using your full name when corresponding also indicates a further disregard to others in the newsgroup, and in your own contribution. If your 38KB file were actually an attachment it would have made your posting over 80K. Fortunately it is not an attachment but is a file on a website. But for anybody to see what the question is you make them look at the file, and the newsgroup archives http://google.com/advanced_group_sea...Excel*&num=100 will not show what the question is so it would not help others with similar questions who were willing to search archives for something close to what they want. Also see http://www.mvps.org/dmcritchie/excel/xlnews.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 "JE McGimpsey" wrote ... Sorry - I don't open unsolicited workbooks (virii, for one thing, and long experience that people usually attach workbooks because they're too lazy to clearly articulate their request, for another). In article wrote: Please find attached the dummy excel to view the requirement. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting
If you just have 3 color choices per cell then you can use conditional
formatting. There are lots of examples and lots of instructions on what to do on my Conditional Formatting page. http://www.mvps.org/dmcritchie/excel/condfmt.htm These are worksheet functions and most worksheet functions are not case sensitive so that makes things easy. For either of two names: =OR(A1="John", "Mike") For a range between "John" and "Mike" inclusive =AND(A1="John", A1<="Mike") For a range between first letter "J" through first letter "M" =AND(LEFT(A1,1)="J", LEFT(A1,1)<="M") --- 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 "kvedogg" wrote in message ... David, I'm new to this bulletin board and don't know the most effective way to get a response to a post. I've posted this question somewhere on the bulletin board but after reading a number of your responses, I thought you might be able to help me. Here's my problem: I'd like to format cells in Excel for 10 different text conditions. At the simple end I can format a cell by saying Cell Value is Equal To ="John". I can do that for 3 different names. I can even trick the system by using Cell Value is Between ="John" and ="Mike" and have 3 different formats for 6 conditions. How would I use conditional formatting to for example, turn the cell blue for a John, Mike or Sara entry, green for a Dick or Mary entry and red for a Jane, Bob, Sally or Paul entry? If I have to use a macro to accomplish this, how would I do it? Any help is appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional formatting--different formatting depending on cell con | Excel Discussion (Misc queries) | |||
Formatting Conditional Formatting Icon Sets | Excel Discussion (Misc queries) | |||
Protect Cell Formatting including Conditional Formatting | Excel Discussion (Misc queries) | |||
conditional Formatting based on cell formatting | Excel Worksheet Functions | |||
Conditional Formatting that will display conditional data | Excel Worksheet Functions |