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

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

dave3009

Well-known Member
Joined
Jun 23, 2006
Messages
7,013
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
76,303
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
 

Forum statistics

Threads
1,141,022
Messages
5,703,791
Members
421,316
Latest member
Cyril Beki

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
Top