| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change Cell Color dependent on Cell Contents
In VBA code, I need to have the background color of the cells in Column H
change depending on Text in the Cell. I can not use conditional formating because I have to many codes and colorsr. Code A-1 background color Green A-2 background color Green G-1 background color Yellow G-2 background color Yellow G-3 background color Orange CA-1 background color Blue GA-1 background color Black GA-2 background color Gray |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Change Cell Color dependent on Cell Contents
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit: Application.EnableEvents = False With Target If .Column = 8 Then Select Case .Value Case "A-1": .Interior.ColorIndex = 10 'Green Case "A-2": .Interior.ColorIndex = 10 'Green Case "G-1": .Interior.ColorIndex = 6 'Yellow Case "G-2": .Interior.ColorIndex = 6 'Yellow Case "G-3": .Interior.ColorIndex = 46 ' Orange Case "CA-1": .Interior.ColorIndex = 5 'Blue Case "GA-1": .Interior.ColorIndex = 1 'Black Case "GA-2": .Interior.ColorIndex = 16 'Gray 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. You might also want to check out this free add-in http://www.xldynamic.com/source/xld.....Download.html -- HTH RP (remove nothere from the email address if mailing direct) "Bill" wrote in message ... In VBA code, I need to have the background color of the cells in Column H change depending on Text in the Cell. I can not use conditional formating because I have to many codes and colorsr. Code A-1 background color Green A-2 background color Green G-1 background color Yellow G-2 background color Yellow G-3 background color Orange CA-1 background color Blue GA-1 background color Black GA-2 background color Gray |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Change Cell Color dependent on Cell Contents
Typo alert
Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False With Target If .Column = 8 Then Select Case .Value Case "A-1": .Interior.ColorIndex = 10 'Green Case "A-2": .Interior.ColorIndex = 10 'Green Case "G-1": .Interior.ColorIndex = 6 'Yellow Case "G-2": .Interior.ColorIndex = 6 'Yellow Case "G-3": .Interior.ColorIndex = 46 ' Orange Case "CA-1": .Interior.ColorIndex = 5 'Blue Case "GA-1": .Interior.ColorIndex = 1 'Black Case "GA-2": .Interior.ColorIndex = 16 'Gray End Select End If End With ws_exit: Application.EnableEvents = True End Sub -- HTH RP (remove nothere from the email address if mailing direct) "Bob Phillips" wrote in message ... Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False With Target If .Column = 8 Then Select Case .Value Case "A-1": .Interior.ColorIndex = 10 'Green Case "A-2": .Interior.ColorIndex = 10 'Green Case "G-1": .Interior.ColorIndex = 6 'Yellow Case "G-2": .Interior.ColorIndex = 6 'Yellow Case "G-3": .Interior.ColorIndex = 46 ' Orange Case "CA-1": .Interior.ColorIndex = 5 'Blue Case "GA-1": .Interior.ColorIndex = 1 'Black Case "GA-2": .Interior.ColorIndex = 16 'Gray 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. You might also want to check out this free add-in http://www.xldynamic.com/source/xld.....Download.html -- HTH RP (remove nothere from the email address if mailing direct) "Bill" wrote in message ... In VBA code, I need to have the background color of the cells in Column H change depending on Text in the Cell. I can not use conditional formating because I have to many codes and colorsr. Code A-1 background color Green A-2 background color Green G-1 background color Yellow G-2 background color Yellow G-3 background color Orange CA-1 background color Blue GA-1 background color Black GA-2 background color Gray |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Change Cell Color dependent on Cell Contents
Thanks. I got the typo but it worked great.
Bill "Bob Phillips" wrote: Typo alert Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False With Target If .Column = 8 Then Select Case .Value Case "A-1": .Interior.ColorIndex = 10 'Green Case "A-2": .Interior.ColorIndex = 10 'Green Case "G-1": .Interior.ColorIndex = 6 'Yellow Case "G-2": .Interior.ColorIndex = 6 'Yellow Case "G-3": .Interior.ColorIndex = 46 ' Orange Case "CA-1": .Interior.ColorIndex = 5 'Blue Case "GA-1": .Interior.ColorIndex = 1 'Black Case "GA-2": .Interior.ColorIndex = 16 'Gray End Select End If End With ws_exit: Application.EnableEvents = True End Sub -- HTH RP (remove nothere from the email address if mailing direct) "Bob Phillips" wrote in message ... Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False With Target If .Column = 8 Then Select Case .Value Case "A-1": .Interior.ColorIndex = 10 'Green Case "A-2": .Interior.ColorIndex = 10 'Green Case "G-1": .Interior.ColorIndex = 6 'Yellow Case "G-2": .Interior.ColorIndex = 6 'Yellow Case "G-3": .Interior.ColorIndex = 46 ' Orange Case "CA-1": .Interior.ColorIndex = 5 'Blue Case "GA-1": .Interior.ColorIndex = 1 'Black Case "GA-2": .Interior.ColorIndex = 16 'Gray 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. You might also want to check out this free add-in http://www.xldynamic.com/source/xld.....Download.html -- HTH RP (remove nothere from the email address if mailing direct) "Bill" wrote in message ... In VBA code, I need to have the background color of the cells in Column H change depending on Text in the Cell. I can not use conditional formating because I have to many codes and colorsr. Code A-1 background color Green A-2 background color Green G-1 background color Yellow G-2 background color Yellow G-3 background color Orange CA-1 background color Blue GA-1 background color Black GA-2 background color Gray |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| directing cell contents dependent on its value | Excel Worksheet Functions | |||
| how to change color of cell based on contents of cell | Excel Discussion (Misc queries) | |||
| Making cell contents dependent on another cell | Excel Worksheet Functions | |||
| Making cell contents dependent on another cell | Excel Discussion (Misc queries) | |||
| Change cell color based on contents | Excel Discussion (Misc queries) | |||
Linear Mode
