Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting for more than 3 conditions
Hi,
I tried downloading from the xldynamic site, but I think I am doing something wrong. I get a zip file, and when I extract files I do not get a ..ini file only a .xls file and nothing happens after that. Can someone help me write a Macro for this? It's simple: I have to look down a range of columns and rows (Say A1: H30) and, based on the text there, which could be one of the following letters: 1) G 2) R 3) N 4) Y 5) C 6) W I need a different color for each type of cell: 1) G=Green with dark green font for the letter, 2) R=Red with red background and black for letter R, 3) N= Purple background and Yellow font, 4) Y=Yellow background, black font, 5) C=Blue background dark blue font 6) W= White background, blue font, I have no clue how to write a Macro, your help will be highly appreciated, thanks -- MMM |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting for more than 3 conditions
It shouldn;t be an xls file but an xla file.
Here is a VBA example that sets the cell colour Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range("A1:H30")) Is Nothing Then With Target Select Case UCase(.Value) Case "B": .Interior.ColorIndex = 5 Case "O": .Interior.ColorIndex = 46 Case "P": .Interior.ColorIndex = 7 Case "R": .Interior.ColorIndex = 3 'etc. End Select End With End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "MMM" wrote in message ... Hi, I tried downloading from the xldynamic site, but I think I am doing something wrong. I get a zip file, and when I extract files I do not get a .ini file only a .xls file and nothing happens after that. Can someone help me write a Macro for this? It's simple: I have to look down a range of columns and rows (Say A1: H30) and, based on the text there, which could be one of the following letters: 1) G 2) R 3) N 4) Y 5) C 6) W I need a different color for each type of cell: 1) G=Green with dark green font for the letter, 2) R=Red with red background and black for letter R, 3) N= Purple background and Yellow font, 4) Y=Yellow background, black font, 5) C=Blue background dark blue font 6) W= White background, blue font, I have no clue how to write a Macro, your help will be highly appreciated, thanks -- MMM |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting for more than 3 conditions
Note that Bob's code will run only when the cell's value is
changed by the user, not, as is the case with Conditional Formatting, when the change is the result of a calculation. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Bob Phillips" wrote in message ... It shouldn;t be an xls file but an xla file. Here is a VBA example that sets the cell colour Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range("A1:H30")) Is Nothing Then With Target Select Case UCase(.Value) Case "B": .Interior.ColorIndex = 5 Case "O": .Interior.ColorIndex = 46 Case "P": .Interior.ColorIndex = 7 Case "R": .Interior.ColorIndex = 3 'etc. End Select End With End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "MMM" wrote in message ... Hi, I tried downloading from the xldynamic site, but I think I am doing something wrong. I get a zip file, and when I extract files I do not get a .ini file only a .xls file and nothing happens after that. Can someone help me write a Macro for this? It's simple: I have to look down a range of columns and rows (Say A1: H30) and, based on the text there, which could be one of the following letters: 1) G 2) R 3) N 4) Y 5) C 6) W I need a different color for each type of cell: 1) G=Green with dark green font for the letter, 2) R=Red with red background and black for letter R, 3) N= Purple background and Yellow font, 4) Y=Yellow background, black font, 5) C=Blue background dark blue font 6) W= White background, blue font, I have no clue how to write a Macro, your help will be highly appreciated, thanks -- MMM |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting for more than 3 conditions
Bob,
Your add-in is absolutely awesome once I got it to work. Kudos! -- MMM "Bob Phillips" wrote: It shouldn;t be an xls file but an xla file. Here is a VBA example that sets the cell colour Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range("A1:H30")) Is Nothing Then With Target Select Case UCase(.Value) Case "B": .Interior.ColorIndex = 5 Case "O": .Interior.ColorIndex = 46 Case "P": .Interior.ColorIndex = 7 Case "R": .Interior.ColorIndex = 3 'etc. End Select End With End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "MMM" wrote in message ... Hi, I tried downloading from the xldynamic site, but I think I am doing something wrong. I get a zip file, and when I extract files I do not get a .ini file only a .xls file and nothing happens after that. Can someone help me write a Macro for this? It's simple: I have to look down a range of columns and rows (Say A1: H30) and, based on the text there, which could be one of the following letters: 1) G 2) R 3) N 4) Y 5) C 6) W I need a different color for each type of cell: 1) G=Green with dark green font for the letter, 2) R=Red with red background and black for letter R, 3) N= Purple background and Yellow font, 4) Y=Yellow background, black font, 5) C=Blue background dark blue font 6) W= White background, blue font, I have no clue how to write a Macro, your help will be highly appreciated, thanks -- MMM |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting for more than 3 conditions
I'm trying to do exactly that--make it change cell color based on the calculated value, and have more than 3 conditions. Is there a modification to this that someone can suggest? Thanks in advance for any insight anyone can offer. Brian Chip Pearson Wrote: Note that Bob's code will run only when the cell's value is changed by the user, not, as is the case with Conditional Formatting, when the change is the result of a calculation. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com -- bkcoughlin ------------------------------------------------------------------------ bkcoughlin's Profile: http://www.excelforum.com/member.php...o&userid=32284 View this thread: http://www.excelforum.com/showthread...hreadid=507761 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
More than 3 Conditional Formatting Conditions | Excel Worksheet Functions | |||
Conditional formatting capability for 20+ conditions not just 3. | Excel Discussion (Misc queries) | |||
Additional Conditions for Conditional Formatting | Excel Worksheet Functions | |||
Conditional formatting: I have five conditions, how to do this? | Excel Discussion (Misc queries) | |||
Adding more than three Conditions to 'Conditional Formatting' | Excel Discussion (Misc queries) |