how to change a cell color based on its content using macro?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
'-----------------------------------------------------------------
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)
 
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
 
Select the cells that you want to format. In this example, we'll
assume that range is A1:A10. With the cells selected, go to the Format
menu and choose Conditional Formatting. In that dialog, change "Cell
Value Is" to "Formula Is" and enter the following formula:

=COUNTIF($A$1:$A$10,A1)>=N

Here, change $A$1:$A$10 to the full range of student names (using the
$ characters as shown) and change the A1 (without $ characters) to the
first cell in the list of names. Change the N to the minimum number of
times that a name need appear in the list to cause the formatting to
be applied.

Click the Format menu and choose formatting to be applied if the
student appears in the lists more than N times.

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


On Mon, 15 Dec 2008 10:58:06 -0800, Christienne Moudden <Christienne
 
I have a similar request. I am using several worksheets and would like to
change the color of one cell when a different cell (or two) in a separate
worksheet is altered. I realize that Excel won't allow conditional formatting
to do this but is there a macro?
I'm using Excel and Windows 7
 
Back
Top