Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
how to change a cell color based on its content using macro?
Hi, I want to automatically change the color of a cell when a user select a
color from a drop-down list. For example: user selects 'green' would change the color of the same cell to green. Appreicate any help to implement this. Thanks |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
how to change a cell color based on its content using macro?
'----------------------------------------------------------------- Private Sub Worksheet_Change(ByVal Target As Range) '----------------------------------------------------------------- Const WS_RANGE As String = "H1" '<=== change to suit On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target Select Case .Value Case "red": .Interior.ColorIndex = 3 'red Case "yellow": .Interior.ColorIndex = 6 'yellow Case "blue": .Interior.ColorIndex = 5 'blue Case "green": .Interior.ColorIndex = 10 'green 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 (there's no email, no snail mail, but somewhere should be gmail in my addy) "Ranger888" wrote in message ... Hi, I want to automatically change the color of a cell when a user select a color from a drop-down list. For example: user selects 'green' would change the color of the same cell to green. Appreicate any help to implement this. Thanks |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
how to change a cell color based on its content using macro?
Hi Bob,
Thanks for taking time to reply, your solution is perfect. Regards, Rod "Bob Phillips" wrote: '----------------------------------------------------------------- Private Sub Worksheet_Change(ByVal Target As Range) '----------------------------------------------------------------- Const WS_RANGE As String = "H1" '<=== change to suit On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target Select Case .Value Case "red": .Interior.ColorIndex = 3 'red Case "yellow": .Interior.ColorIndex = 6 'yellow Case "blue": .Interior.ColorIndex = 5 'blue Case "green": .Interior.ColorIndex = 10 'green 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 (there's no email, no snail mail, but somewhere should be gmail in my addy) "Ranger888" wrote in message ... Hi, I want to automatically change the color of a cell when a user select a color from a drop-down list. For example: user selects 'green' would change the color of the same cell to green. Appreicate any help to implement this. Thanks |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
how to change a cell color based on its content using macro?
Is it possible to use the fill formatting, using an InputBox return,
triggered in sub Worksheet_Change, i.e. not based on the field's content, but on a users input following the entry of text? "Bob Phillips" wrote: '----------------------------------------------------------------- Private Sub Worksheet_Change(ByVal Target As Range) '----------------------------------------------------------------- Const WS_RANGE As String = "H1" '<=== change to suit On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target Select Case .Value Case "red": .Interior.ColorIndex = 3 'red Case "yellow": .Interior.ColorIndex = 6 'yellow Case "blue": .Interior.ColorIndex = 5 'blue Case "green": .Interior.ColorIndex = 10 'green 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 (there's no email, no snail mail, but somewhere should be gmail in my addy) "Ranger888" wrote in message ... Hi, I want to automatically change the color of a cell when a user select a color from a drop-down list. For example: user selects 'green' would change the color of the same cell to green. Appreicate any help to implement this. Thanks |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
how to change a cell color based on its content using macro?
I have a similar (I think) question and you seem to know what you're talking
about :) I teach high school and have a spreadsheet with students' names listed each time they're sent to the "Behavior Improvement Center" for infractions of rules or dress code violations. The man in charge of the center wants to know if there is a way to automatically change the color of the student's name when it has been input into the spreadsheet more than a certain number of times, as there are consequences such as suspension, etc. I have been using Excel for 20 years and am the guru around here, but I can't seem to figure out a way to do this and don't even know if it's possible. Any suggestions would be appreciated, Christienne Moudden "Bob Phillips" wrote: '----------------------------------------------------------------- Private Sub Worksheet_Change(ByVal Target As Range) '----------------------------------------------------------------- Const WS_RANGE As String = "H1" '<=== change to suit On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target Select Case .Value Case "red": .Interior.ColorIndex = 3 'red Case "yellow": .Interior.ColorIndex = 6 'yellow Case "blue": .Interior.ColorIndex = 5 'blue Case "green": .Interior.ColorIndex = 10 'green 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 (there's no email, no snail mail, but somewhere should be gmail in my addy) "Ranger888" wrote in message ... Hi, I want to automatically change the color of a cell when a user select a color from a drop-down list. For example: user selects 'green' would change the color of the same cell to green. Appreicate any help to implement this. Thanks |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
how to change a cell color based on its content using macro?
Wow -- you're amazing !!! Thank you so much !!!
Christienne "Ranger888" wrote: Hi, I want to automatically change the color of a cell when a user select a color from a drop-down list. For example: user selects 'green' would change the color of the same cell to green. Appreicate any help to implement this. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel: Syntax to change cell color based on color of another cell | Excel Worksheet Functions | |||
how do I change the content of one cell based on another? | Excel Discussion (Misc queries) | |||
Change the data in one cell based on content of another. | Excel Discussion (Misc queries) | |||
Change tab color based on a cell value | Excel Discussion (Misc queries) | |||
How do I change content of a cell based on date | Excel Worksheet Functions |