How to use drop box selection to change cell color.

A

arcticman141

New Member
Joined
Apr 16, 2011
Messages
5
Howdy.. I am sure this has been answered somewhere, but after four hours of surfing I can't seem to get it. So

I need to have a drop box selection change the color of the cell, but not change the value of it. The options in the drop box will be text but I need the result to be a change of color in target cell. Also need to have other options change the text color only of the cell. I am pretty new with excel, have spent alot of time tonight researching to no avail. I am running excel 2000 but have access to 2007 also. And need this to run on multiple cells. I have created the drop box cells F1-F5 using Data Validation -> List, Example of what I'm looking for

Cell A1= 16

Drop Box Option- Tardy = Change A1 to Red Background
Drop Box Option- Absent = Change A1 to Yellow Background
Drop Box Opton - Attended = Change A1 to Blue Background
Drop Box Opton - 5th = Change A1 to Blue Text
Drop Box Opton - 6th = Change A1 to Red Text

Your help is much appreciated.
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Sort by date Sort by votes
Forgot, I also need to be able to change the value of the cell A1 at any time and keep the formatting beset from the drop box!!!
 
Upvote 0
Welcome to the Board!

Take a look at Conditional Formatting in the helpfile. In 2000 you only have 3 options, so you'd have to use a Change event there, but 2007+ supports up to 64 levels of formatting.

If you use the formula option you can set A1 to be something like this: =A1="Tardy", then format as desired.

Post back if you'd rather stick to 2000 and I'll post some code for that.

HTH,
 
Upvote 0
What cell or Column is your dropdown(s) in?? Do you want to format the corresponding cell in Column "A"??

lenze
 
Upvote 0
Try this: right click the sheet tab, select View Code and paste in

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("F1:F5")) Is Nothing Then Exit Sub
With Range("A1")
    Select Case Target.Value
        Case "Tardy": .Interior.ColorIndex = 3
        Case "Absent": .Interior.ColorIndex = 6
        Case "Attended": .Interior.ColorIndex = 5
        Case "5th": .Font.ColorIndex = 5
        Case "6th": .Font.ColorIndex = 3
    End Select
End With
End Sub
 
Upvote 0
Nothing happened with that code, It is just replacing the value in the cell with the text from the drop down, no colors ie; plain text.
The conditional formatting is getting the cell color changed, but is also changing the cell content to that of the drop box pick.. I need the cell value to remain the same and even be able to type in new number when needed..

Dropdowns are in F1:F5 and need to correspond to every dropdown on the sheet... multiple cells need the same dropdown menu with the same resulting color changes with no effect to the values typed into the cell! Not sure if that makes sense..

thanks, this is a wonderful site and you'all that help people are great americans!! or just great people if not american :) thanks again.
 
Upvote 0
Yes Sir.... Put the code in and essentially nothing happened. dropdown pick is putting the text (Tardy) in the cell and no color changing..
Did you mean that what I want can't happen?

After entering the code into the corresponding worksheet do I need to do anything esle or just exit out?
 
Upvote 0
Hello

Typo, try this.

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
If [COLOR="Red"]Not[/COLOR] Intersect(Target, Range("F1:F5")) Is Nothing Then Exit Sub
With Range("A1")
    Select Case Target.Value
        Case "Tardy": .Interior.ColorIndex = 3
        Case "Absent": .Interior.ColorIndex = 6
        Case "Attended": .Interior.ColorIndex = 5
        Case "5th": .Font.ColorIndex = 5
        Case "6th": .Font.ColorIndex = 3
    End Select
End With
End Sub
 
Upvote 0
Yes that is working to an effect, but not when used with a dropdown menu, only when I type the text (tardy) does the cell color change..

I don't want the text in the cell, I need a different value - something of my choice - and want the the option, when selected from a dropdown menu to change the cell and leave the value in the cell not apply the actual selected text, just the formating (color change)

It sure is amazing what you guys can do with this program though
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question
VBA Loop Through Data Validation Drop Down List
Replies
5
Views
332
rlv01
R
J
  • Solved
Auto-populate cell if data validation list has only one option
Replies
7
Views
217
Jeffrey Mahoney
Jeffrey Mahoney
R
  • Question
Data Validation Drop Down List - Allow Duplicates
Replies
5
Views
758
ronie85
R
B
  • Question
Event code to give message box and change color of auto-shape
Replies
4
Views
149
bh24524
B
D
  • Question
Dropdown list that is not data validation
Replies
1
Views
620
delaney1102
D

Forum statistics

Threads
1,214,790
Messages
6,121,608
Members
449,038
Latest member
apwr

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top