Change colors of a cell, based on entry?

webmeister

New Member
Joined
Sep 13, 2006
Messages
38
Yesterday, I asked about how to change the color of a row, based upon what value was selected in a drop-down cell. Thank you to the wonderful folk who responded and helped tremendously! I was able to create a workbook-wide macro after tweaking the code that was suggested.

Is there a way to tie code only to that drop-down cell? In other words, say I have 5 values in the drop-down, "Accepted", "Declined", "Cancelled" and so on. When I choose one of these values from the drop-down, I want the row's color to change immediately after a value is picked.

I simply don't know how to tie code to a specific cell.

If anyone is willing to offer assistance, I sure would be happy!

Thank you to all who reply.....looking forward to it!!
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

dave3009

Well-known Member
Joined
Jun 23, 2006
Messages
7,002
Office Version
  1. 365
  2. 2016
Sounds like a job for conditional formatting, only that this currently limited to 3 colour selections. I have read code that allows single cell colour changes with much more colours. I'll post back if I find the link.

Hope this helps.


Dave
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,915
Office Version
  1. 365
Platform
  1. Windows
webmeister

It might be an idea to continue in the original thread, then we can see the code already supplied.
 

webmeister

New Member
Joined
Sep 13, 2006
Messages
38
Dave3009,

Thanks for replying! Yeah, I new about the conditional formatting, which is why I'm looking for help on applying code to a single cell......
If you find the code you were talking about, definitely let me know!

Thanks again!!!
 

Cbrine

Well-known Member
Joined
Dec 2, 2003
Messages
3,196

ADVERTISEMENT

Conditional Formatting is limited to 3 conditions. To get past this you will need to use VBA. Post this to the vbe in the worksheet you wish it to run on. You will need to update the Activesheet.range("A1") to your target cell.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not (Intersect(Target, ActiveSheet.Range("A1")) Is Nothing) Then
    Select Case Target
        Case "Accepted"
            Target.Interior.ColorIndex = 5
        Case "Declined"
            Target.Interior.ColorIndex = 10
        Case "Cancelled"
            Target.Interior.ColorIndex = 15
    End Select
End If
End Sub

HTH
Cal
 

webmeister

New Member
Joined
Sep 13, 2006
Messages
38
Norie,

I will keep that in mind for follow-up postings. Thanks for the suggestion!

CBrine,

Thanks for the comments and for the code. That was what I needed to see. I'll tweak it and see what falls out the other side.

Thanks to all of you folk for replting so quickly!! Unbelievable! :LOL:
 

webmeister

New Member
Joined
Sep 13, 2006
Messages
38

ADVERTISEMENT

CBrine,

How would I change that reference to "A1" so that the code would sense what cell I'm on? I.E., if I am on cell N4, check for its value, if I'm on cell N99, check for its value and so forth?

Thanks! Looking forward to your reply!!
 

Cbrine

Well-known Member
Joined
Dec 2, 2003
Messages
3,196
Webmeister,

The code already does that. It compares Target(Your ActiveCell) to Range("A1"). If Target and Range("A1") are equal the code is then executed. If want to do multiple ranges, just add them to the Intersect()

ie.
If Not (Intersect(Target, ActiveSheet.Range("A1"),Range("Z43")) Is Nothing) Then

HTH
Cal

If you want different actions or formatting to occur for multiple ranges, then I would suggest a select case on the target.address.

Code:
Private Sub Worksheet_Change(ByVal Target As Range) 
If Not (Intersect(Target, Range("A1"),range("N9")) Is Nothing) Then 
Select Case Target.address
Case "$A$1"
    Select Case Target 
        Case "Accepted" 
            Target.Interior.ColorIndex = 5 
        Case "Declined" 
            Target.Interior.ColorIndex = 10 
        Case "Cancelled" 
            Target.Interior.ColorIndex = 15 
    End Select 
Case "$N$9"
Select Case Target 
        Case "Accepted" 
            Target.Interior.ColorIndex = 30
        Case "Declined" 
            Target.Interior.ColorIndex = 35 
        Case "Cancelled" 
            Target.Interior.ColorIndex = 45 
    End Select 
end select
End If 
End Sub
 

webmeister

New Member
Joined
Sep 13, 2006
Messages
38
CBrine,

Thanks! I'm just trying to wrap my head around this stuff :eek: So, let's see if I have this straight..... I have to check my target cell against a value in a specific range, right? What if I have several possible values? For example, if I pick value1 from my drop-down cell, I want one color (so I would need a matching value in a specific range), but if I pickanother value from my drop-down, I want a different color (so I would now need a second value in another range), and so forth. Correct me if I'm wrong..... would I therefore need a range equal to the number of choices that my drop-down cell has?

Next question.... why do I need a range at all..... can't I just modify the code to something like:

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target
Case "Accepted"
Target.Interior.ColorIndex = 5
Case "Declined"
Target.Interior.ColorIndex = 10
Case "Cancelled"
Target.Interior.ColorIndex = 15
End Select
End Sub


Thanks again for helping....I really appreciate your interest in my efforts and attempts!!
 

Cbrine

Well-known Member
Joined
Dec 2, 2003
Messages
3,196
OK, now I'm confused:).

If you want to have the SAME vba formatting, on a series of different cells, you would add the other ranges into the Intersect() method.

Code:
Private Sub Worksheet_Change(ByVal Target As Range) 
If Not (Intersect(Target, Range("A1"),Range("N9")) Is Nothing) Then 
    Select Case Target 
        Case "Accepted" 
            Target.Interior.ColorIndex = 5 
        Case "Declined" 
            Target.Interior.ColorIndex = 10 
        Case "Cancelled" 
            Target.Interior.ColorIndex = 15 
    End Select 
End If 
End Sub

If you wanted DIFFERENT formatting on a series of different cells. You would do this.

Code:
Private Sub Worksheet_Change(ByVal Target As Range) 
If Not (Intersect(Target, Range("A1"),range("N9")) Is Nothing) Then 
Select Case Target.address 
Case "$A$1" 
    Select Case Target 
        Case "Accepted" 
            Target.Interior.ColorIndex = 5 
        Case "Declined" 
            Target.Interior.ColorIndex = 10 
        Case "Cancelled" 
            Target.Interior.ColorIndex = 15 
    End Select 
Case "$N$9" 
Select Case Target 
        Case "Billing" 
            Target.Interior.ColorIndex = 30 
        Case "Shipping" 
            Target.Interior.ColorIndex = 35 
        Case "Cost" 
            Target.Interior.ColorIndex = 45 
    End Select 
end select 
End If 
End Sub

HTH
Cal
 

Watch MrExcel Video

Forum statistics

Threads
1,113,952
Messages
5,545,143
Members
410,666
Latest member
Al3cs
Top