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

 

[webmeister]

As far as confused, that makes two of us!! I'll try explaining again:

In my worksheet, I have one column and in each drop-down cell of that one column, I can have one of five different values: ("Active", "Awarded", "Rejected", "Cancelled" or "Withdrawn."

Depending on what is chosen from any row's cell in that column that has these drop down choices, the row will be given a different color. The column that has the drop-down cells is column "N".

So, at this point, what I want to have happen is that in column "N", no mater if the cell is N1, N6, N99, N143, or any other drop-down cell in column N, when one of the five values previously mentioned is chosen, I immediately want that cell's row to change to a predetermined color.

Hopefully, I've made a better explanation???

I'm also not completely understanding in your code, why there has to be a value in another cell to compare against.

Thanks once more..... I appreciate your interest and help!!

 

[Cbrine]

webmeister,
That was perfect. I just used Range("A1") as a default value where the drop down would be. It wasn't looking at that value, it was applying the formatting to that cell. It was only meant as an example on how to use the intersect method. To do what you want you would do this.

Private Sub Worksheet_Change(ByVal Target As Range) 
If Not (Intersect(Target,Range("N:N")) Is Nothing) Then 
    Select Case Target 
        Case "Active" 
            Target.Interior.ColorIndex = 5 
        Case "Awarded" 
            Target.Interior.ColorIndex = 10 
        Case "Rejected" 
            Target.Interior.ColorIndex = 15 
        Case "Cancelled"
             Target.interior.colorindex = 20
        Case "Withdrawn"
              Target.interior.colorindex =25
    End Select 
End If 
End Sub

HTH
Cal

 

[webmeister]

And.....thank you again!

I hope my questions start winding down quickly, so I don't bother too much!

Anyway.... where exactly do I place that code? I hit F11 in my worksheet to get to the VBE, place the code there, save it but then nothing happens! I've hidden my Personal.XLS (at least I think I have) and tried several times, but nothing seems to be happening.

Any ideas or comments on what an apparent newbie is doing wrong?

 

[Cbrine]

Np,
Your on the right track.

After you open the VBE, use project explorer to find the sheet you want the code to act on. Double click on it and paste the code to the code window. Make sure you are not in design mode in excel and give it a try.

HTH
Cal

 

[webmeister]

Got it!! It's working. Now all I've got to do is mess around with the colors and it should be fair saling.

Thanks for all your help! That was great!! I'm going home and have a bottle or four of wine to celebrate!!

 

[Cbrine]

Glad I could help.

To make it a little easier for you give this a try

Target.Interior.Color =vbRed

I always use colorindex because of the flexability, but color is easier to use. Just check the online help for the other vb constants.

 

[webmeister]

Oops....one last question:

What is the format of the line to change an entire row as opposed to a cell to a different color? For example:

Target.Interior.ColorIndex = 5 will change a cell.... what's its equivalent to change a whole row? And, where can I dig up more information on these tyes of things?

And thanks for the vbColor hint - that was nice! I'll check on the other vbconstants, as you suggested.

 

[Cbrine]

Webmeister,
This will color the entire row.
Rows(Target.row).EntireRow.Interior.Color = vbRed

As far as learning this stuff, MR Excel would be the first step, and you are already there! Take a look at the online store here, or pick up an Excel VBA book from a bookstore. Use that as a reference, and post here when ever you have questions.

HTH
Cal

 

[webmeister]

CBrine,

Again I thank you for your patience and for your help! I've been in other forums where some of the so-called experts would reply with answers like, "That question has already been posted in one form or another. Do a search on the forum and you should find what you need." Talk about wasting time and bandwidth!!

Nice to see a forum where I can get replies and help so very fast. Thanks again!