sheet tab color change macro

[kendel]

Hello, I'm using Excel 2007 and Windows XP

Basically I'd like to change or not change the sheet tab color automatically dependent on a cell's content or lack of content.

I'm using an IF formula that enters a "1" or it remains an empty cell. I want to change that sheet tab color depedent on the "1", say green and no change for the blank. I would like the macro to be run automatically when the cell is populated. The sheet is used to record data by the Data Entry dept and several cells contain formulas based on the data. When bad data is entered, the cell that contains the IF formula will display the 1.

When I open the workbook I'll be able to see at a glance which sheets need attention and correct the bad data.

Thanks for any help with this as I have no idea how this could be done.
Kendel

 

[tygrrboi]

I would suggest putting a worksheet change event in the place where you last manually input a number or make a selection. From what I can gather (not confident I completely follow that structure you just explained...), many of the changes to the analytics sheets happens via formulas... But you want the tab colour to change when a formula results in a different answer...

Even if you have to go back to sheet1 column B... Set the trigger for the worksheet change to the thing that you would have to last touch... Then cycle through each worksheet, checking the value of H3 each time and assigning a colour to the tab.

 

[tygrrboi]

sheet 1 :

Private Sub Worksheet_Change(ByVal Target As Excel.Range) 
    If Target.Column = 2 Then 
        With Application
            .EnableEvents = False
            .ScreenUpdating = False
        End With

        Dim ws as Worksheet
        For Each ws in ActiveWorkbook.Worksheets
            Select Case ws.Range("H3").Value
            Case is = "red"
               ws.Tab.Color = vbRed
            Case is = "yellow"
               ws.Tab.Color = vbYellow
            Case is = "green"
               ws.Tab.Color = "vbGreen
            Case Else
               'do nothing
            End Select

        With Application
            .EnableEvents = True
            .ScreenUpdating = True
        End With
        Next ws
    End If 
End Sub

something like that... ?

 

[asggold]

I appreciate the help but I must be missing something here. Neither of these codes seem to do anything when I make changes. For all the other formatting everything changes throughout the entire workbook when I make a chance on the "Data Collection" sheet. For example: If I change cell B2 to "Direct" on the "Data Collection" sheet it turns cell A2 on the "Data Collection" sheet Green (fill). That also triggers whatever word is in cell A2 to change the text color of that word to Green on every other tab in the workbook. The end result of all these changes to the dropdown in column B cells of the "Data Collection" sheet is setting cell H3 on all the other tabs to either "Red", Yellow" or "Green". This is the cell which should trigger the tab color.

I tested the module code by making a copy of my current Workbook, changing all the dropdown cells in column B of the "Data Collection" sheet to "Direct". This should have turned all the tabs Green. Everything else turned Green and cell H3 on each of the tabs said Green, but none of the tabs turned green. I even went into each sheet trying to click F9 or any other button to see if something would happen...nothing.

Just to make sure it had nothing to do with a conflict from the sheet code I already had for each sheet I deleted the code from the first few sheets. Still nothing.

Any ideas what I am doing wrong? I thought it would be easier to change the tab color based on a cell in that sheet/tab. If it is easier to trigger all the tab colors individually from a cell in one separate sheet, I can do that too. The H3 cell is just pulling the word "Red", Yellow", or "Green" from another cell on a different sheet anyway.

Thanks!

 

[My Aswer Is This]

We are now up to post # 23 now and still have not been able to get you what you want.

You said each sheet in cell "H3" you had either "Green" "Red" or "Yellow"

The script I sent you does that. If H3 has the value "Green" then the tab color turns Green.

I test all my scripts and like helping people but cannot understand why you keep saying nothing happens.

Are you sure you know to activate a Module script?

This script requires you to click a button or a use a shortcut key.

I would assume you know the basic ways to run scripts or you would not be here asking for script to be written for you.

And when mentioning scripts that do not work for you you need to say script in post 3 does not work. Or what ever number.

 

[asggold]

I appreciate your help. I am not an expert on coding in Excel but do understand the basics (as I have been able to build all the rest of the parts of this workbook).

I do think we are getting closer but here is the issue with the code still: In order to make the tab color to change you STILL need to go into each tab and change SOMETHING in one of the cells on that tab. If I go into any of the cells of a tab I can now delete a cell, click "undo" and the tab changes color. So, it is not just clicking something which triggers that color change. How do you make a shortcut key? Maybe that is what I need to do? If I make a shortcut key to trigger this "module" then (if I understand you correctly) I would just need to go to every sheet/tab and click this shortcut key and the tab would change color. Correct?

Thanks!

 

[asggold]

OK. I figured out what I was doing wrong and your code from post #18 does work.

Again, sorry for being new at this but running and building VB code is not my specialty. Once I go into the "module" and click the "run" button, it did change all the tabs to the correct color.

Could you tell me how to do this with a shortcut key instead of going into the module and clicking "run"?

Thanks again!

 

[My Aswer Is This]

You must have a sheet change event script in your sheet if you say the script runs automatically when you delete some cell.

You said:
"In order to make the tab color to change you STILL need to go into each tab and change SOMETHING in one of the cells on that tab. If I go into any of the cells of a tab I can now delete a cell, click "undo" and the tab changes color"

You should remove the sheet change event script.

With my script you only need to run it once and it changes all sheet tab colors.

Did you really write your sheet change event scripts and install them.

If you do not know how to run a Module script you should read up some on how to do things like this. I'm not able here to teach you all these procedures.

Try reading up on how to do this here:
How to Run an Excel 2010 Macro - dummies

 

[tygrrboi]

You have three options.

1. Go to the Macro window, find the macro that you just made with MyAnswer's code, click options and set a key combination to that macro (Control Alt Shift A or something like that...)

2. Create a button on your main sheet, and assign MyAnswer's macro to that button. (Right click the button and choose assign macro).

3. Create a Worksheet Change event in the SHEET 1 code (not in the individual sheets that change color) and put something like

Private Sub Worksheet_Change(ByVal Target As Excel.Range) 
If Target.CountLarge > 1 then Exit Sub
IF Target.Column = 2 Then Call Test() 'or whatever you named MyAnswer's macro... 
End Sub

 

[asggold]

Thanks tygrrboi ! That is exactly what I was looking for.

I will try the button option and assign the macro to that.

 

[asggold]

OK, one last question tonight I promise...

I got the button to work with macro from MyAswer

The only additional question is if there is a way in the code, or maybe just with another button, to add what would be equivalent to clicking the F9 button? The reason I ask is because it seems that once I make the changes to the dropdowns that trigger the rest of the workbook changes it will not update the cells in the rest of the workbook until I click the F9 button. Really not a big deal as clicking F9 and then the button to run the macro is MUCH better that what I had to do before.