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

 

[karenwwer3]

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

 

[kendel]

Hi Karen, I got this from another thread<HR style="BACKGROUND-COLOR: #ffffff; COLOR: #ffffff" SIZE=1><!-- / icon and title --><!-- message -->
Press ALT + F11 to open the Visual Basic Editor. In the Project window double click ThisWorkbook and paste in

Code:


Private Sub Workbook_Open()
Call MyMacro
End Sub


</PRE>
Change MyMacro to the name of the macro that you want to run.

 

[asggold]

I know this is an old thread but hoping someone might be able to help. I have a workbook with the following script in each sheet managing the color of the tab:
----------------------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
MyVal = Range("H3").Text


With ActiveSheet.Tab
Select Case MyVal
Case "Red"
.Color = vbRed
Case "Green"
.Color = vbGreen
Case "Yellow"
.Color = vbYellow
Case Else
.ColorIndex = xlColorIndexNone
End Select
End With
End Sub
-------------------------------------------------------------------

This is working great...with one exception. Once the other cells change that trigger this tab color, I need to hit F9 or go into the cell I am triggering the tab color from and click "enter" to actually get the tab color to change.

Any idea how I can get this to auto update, basically making the workbook ALWAYS look for changes?

Thanks!

 

[My Aswer Is This]

You should tell us what cell change causes this to trigger.
You said:
Once the other cells change that trigger this tab color
what other cells and are they changed by some manual entry some place.

 

[asggold]

Sorry. Here is how it works so far. Again not sure this is the most optimal way but best I could do with my knowledge so far:

1. In Sheet 1 there is a list in column A of data elements. In column B of that same sheet there is a drop-down in each cell (same of reach cell in the column) with 5 choices. Depending on the choice they choose from column B, Column A cell will turn red, yellow, or green.
2. Once the color changes for the cell in column A, it wills in a number in column L (on the same sheet) representing that color (For ex. green=5, yellow=13, red=22)
3. From there, I have different sheets for each "analytic" containing all the cells from Sheet 1, Column A that build that "analytic".
4. In each of those "analytic" sheets the number from Sheet 1, column L will automatically mark a column labeled "Green", "Yellow", or "Red". (sample of function is: =IF('Data Collection'!L4=13, 1,0 --- Data Collection is the name of my Sheet 1) Those are in column J(Green), K(Yellow), & L(Red) on each sheet
5. Those numbers fill in the totals for each color in cells D3 (= SUM of Green column), E3 (= SUM of Yellow column), F3 (=SUM of Red column) on each sheet.
6. The numbers for each of those totals are then brought to a different sheet ("Current Analytics") that shows all the names of the "analytics" (same as tab names) in column A, column B are the Green totals for that sheet, column C is the Yellow totals for that sheet, and column D are the Red totals for that sheet.
7. At that point, I have a function in column F on the "Current Analytics" tab which says: =IF(D2>0,"Red",IF(C2>0, "Yellow", "Green"))
5. Once those are triggered, I have cell H3 on every "analytics" sheet pulling the answer (Red, Yellow, or Green) to that sheet.

That cell (H3 on each of the "analytics" sheets) is what the tab color is triggered off of.

Hope that helps to better explain.

 

[My Aswer Is This]

This is way more then I can digest. Maybe someone else here at Mr Excel will be able to help you.

Sorry. Here is how it works so far. Again not sure this is the most optimal way but best I could do with my knowledge so far:

1. In Sheet 1 there is a list in column A of data elements. In column B of that same sheet there is a drop-down in each cell (same of reach cell in the column) with 5 choices. Depending on the choice they choose from column B, Column A cell will turn red, yellow, or green.
2. Once the color changes for the cell in column A, it wills in a number in column L (on the same sheet) representing that color (For ex. green=5, yellow=13, red=22)
3. From there, I have different sheets for each "analytic" containing all the cells from Sheet 1, Column A that build that "analytic".
4. In each of those "analytic" sheets the number from Sheet 1, column L will automatically mark a column labeled "Green", "Yellow", or "Red". (sample of function is: =IF('Data Collection'!L4=13, 1,0 --- Data Collection is the name of my Sheet 1) Those are in column J(Green), K(Yellow), & L(Red) on each sheet
5. Those numbers fill in the totals for each color in cells D3 (= SUM of Green column), E3 (= SUM of Yellow column), F3 (=SUM of Red column) on each sheet.
6. The numbers for each of those totals are then brought to a different sheet ("Current Analytics") that shows all the names of the "analytics" (same as tab names) in column A, column B are the Green totals for that sheet, column C is the Yellow totals for that sheet, and column D are the Red totals for that sheet.
7. At that point, I have a function in column F on the "Current Analytics" tab which says: =IF(D2>0,"Red",IF(C2>0, "Yellow", "Green"))
5. Once those are triggered, I have cell H3 on every "analytics" sheet pulling the answer (Red, Yellow, or Green) to that sheet.

That cell (H3 on each of the "analytics" sheets) is what the tab color is triggered off of.

Hope that helps to better explain.

 

[asggold]

Maybe I should just ask this making it a lot more simple:

ALL I am looking to do is to color the TAB of that sheet based on the value in cell H3.

The options for values in H3 are ONLY:
"Red"
"Yellow"
"Green"

I will also restate that I am able to get the tab of the sheet to change colors without a problem. The only problem I am having is that once cell H3 changes, in order to get the tab to actually change color I need to click into the cell and hit "enter". I am looking for a way to do this where the color will automatically change or by running a macro to look at all the H3 cells in every sheet and update at once. Having to go into the cell and click "enter" is almost more difficult then just manually changing the tab color every time.

Thanks!

 

[My Aswer Is This]

I think you need to use this script:
This is a module script.

Sub Test()
Application.ScreenUpdating = False
Dim i As Long
    For i = 1 To Sheets.Count
    
        MyVal = Sheets(i).Range("H3").Text
        With Sheets(i).Tab
            Select Case MyVal
                Case "Red"
                    .Color = vbRed
                 Case "Yellow"
                    .Color = vbYellow
                Case "Green"
                    .Color = vbGreen
            
            End Select
        End With
    Next
Application.ScreenUpdating = True
End Sub

 

[asggold]

Thanks! I will give that a shot. It looks almost the same as what I was using but there are a few differences which might do the trick.

I am assuming I put this script on each sheet?

 

[My Aswer Is This]

No this is not a sheet event script.
This is a Module script.
It's run when you press a button or use a shortcut key.

Right click on sheet tab.
Choose View Code
On left upper corner of screen choose insert choose module and paste in the code.