Changing tab color based on cell value

[taab0412]

Hi all,

I am using Excel 2010 and trying to change/edit the color of the tabs in my workbook to turn green or red based on a y or n placed in a cell (the same cell on each tab). I have tried variations on several themes others have asked about as well for Excel 2007 and attempted to adapt them to fit my situation but none seem to work. Here's what I was starting with:

Private Sub Worksheet_Change(ByVal Target As Range)

Select Case Range("A1").Value
Case Is < 2.5
Sheet1.Tab.Color = vbRed

Case Is > 2. 5, Is < 4
Sheet1.Tab.Color = vbGreen
End Select

End Sub

The cell I'm using is F2 and my cell value is simply y or n. Can someone assist me in finding a solution to this? I realize the example above references numeric values and greater than/less than options, but I'm not sure how to correct this for my need.

Thanks so much in advance!
Tim

[Olly]

Try:

Please Login or Register  to view this content.

Edit: If you want this to apply to ALL worksheets in the workbook, without having to copy the code into every worksheet module, then put this in the Workbook module:

Please Login or Register  to view this content.

[TMS]

Please Login or Register  to view this content.

Regards, TMS

[taab0412]

Olly,

I tried your first option. I copied the code and then I went to my tab, right clicked and chose View Code. I pasted the code in and then closed the window. Saved my spreadsheet and then entered a value of 'y' (minus the quotes) in the cell F2 and nothing happened. Did I do this wrong?

When I use the code to affect the whole workbook, it works just fine. Any idea on what I'm doing wrong when I try to apply only to each worksheet in the workbook?

[Olly]

My apologies - I edited shortly after posting, to include a UCase function, which will accept either "y" or "Y". I should have made my edit clear. Thanks to TMS for prompting me to include that bit!

[TMS]

I think the comparison is case sensitive.

Please Login or Register  to view this content.

Regards, TMS

[taab0412]

So TMS,

Your submission is for each worksheet, correct?


TMS/Olly,
Is it possible to also edit/affect the shade of green or red? I'm trying to match the color I chose from the palate for a given cell where I signify Y or N.

Again, thank you...
Tim

[Olly]

Yes, replace vbRed with RGB(255,0,0) - tweak RGB values to suit.

[TMS]

The code would work for any worksheet, as would Olly's, if you copy it to the class module for each sheet where you want it to be effective.

Alternatively, you could put the following slightly modified code into the Workbook Class Module and it will work for EVERY sheet (as coded):

Please Login or Register  to view this content.

Regards, TMS

[TMS]

If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

[taab0412]

Thank you guys SO MUCH! Really appreciate your help. This completely answered my questions.

Tim

[henrik2]

Hey i could use that stuff but with some other options.
is it possibel to look over 3 options..

option 1 if cell value is higher than 8 then (Gold color if possibel)
option 2 if cell value is 8 then Silver (color if possibel)
option 3 if cell value is Under 8 then (Bronze If posibel)

And if nothing in cell then no Tab color .

[TMS]

@henrik2: welcome to the forum.


Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.