How to use drop box selection to change cell color.

[arcticman141]

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.

 

[arcticman141]

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!!!

 

[Smitty]

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,

 

[lenze]

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

lenze

 

[VoG]

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

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

 

[arcticman141]

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.

 

[VoG]

If you are referring to my code, that cannot possibly happen.

 

[arcticman141]

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?

 

[meldoc]

Hello

Typo, try this.

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

 

[arcticman141]

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