Change colors of a cell, based on entry?

[webmeister]

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

 

[dave3009]

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]

webmeister

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

 

[webmeister]

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]

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.

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]

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!

 

[webmeister]

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]

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.

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]

CBrine,

Thanks! I'm just trying to wrap my head around this stuff 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]

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.

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.

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