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

 

[bertie]

Try placing this worksheet change event in the sheet1 module:

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] Worksheet_Change([COLOR=darkblue]ByVal[/COLOR] Target [COLOR=darkblue]As[/COLOR] Range)
   [COLOR=darkblue]With[/COLOR] Sheets("Sheet1")
      [COLOR=darkblue]If[/COLOR] Range("a1").Value = 1 [COLOR=darkblue]Then[/COLOR]
         .Tab.ColorIndex = 4 [COLOR=green]'green[/COLOR]
      [COLOR=darkblue]Else[/COLOR]
         .Tab.ColorIndex = xlNone
      [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
   [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
End [COLOR=darkblue]Sub[/COLOR]

 

[kendel]

Hi bertie, thanks for your response.
I copied the sub into a module, but it's not working.
I have a couple questions if you don't mind
in the line: 'With Sheets ("Sheet1")', If it worked, would it have worked in all the sheets of the workbook?
in the line: 'If Range ("a1"). Value = 1 Then', what does "a1" refer to? Should I replace that with the actual range ("P:P") that contains the cells where the IF function that generates the '1' is?
and the last question, maybe ,
in the line: '.Tab.ColorIndex = x1None' what does x1None mean? I hope it means don't change the tab color at all.

Thanks again for your response!

 

[bertie]

Below is a revised version of the code i submitted earlier. This only triggers when a cell in column "P" (16) is changed. I don't think this is what you are looking for, however. Let me explain how this works.

This is a worksheet change event. It should be placed in a worksheet module.
For it to work on all worksheets the code would have to be copied to all worksheets.
.tab.ColorIndex=xlNone means it remains at the default colour

The reason I don't think this is what you are looking for is that if a cell changes in column "P" this would trigger the change event. And this would overwrite any previous changes:
e.g.
p1 = 1 changes the tab colour to green
p2 = 2 changes the tab colour to default, overwriting the change caused by p1=1

Therefore, you may miss what you are trying to highlight.

Can you post some sample data with an explanation of what trigers the tab colour to change?

[COLOR=darkblue]Option[/COLOR] [COLOR=darkblue]Explicit[/COLOR]
[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] Worksheet_Change([COLOR=darkblue]ByVal[/COLOR] Target [COLOR=darkblue]As[/COLOR] Range)
 
      [COLOR=green]'limit to column "P"[/COLOR]
      [COLOR=darkblue]If[/COLOR] [COLOR=darkblue]Not[/COLOR] Target.Column = 16 [COLOR=darkblue]Then[/COLOR] [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
[COLOR=green]'Stop[/COLOR]
      [COLOR=darkblue]If[/COLOR] Target.Value = 1 [COLOR=darkblue]Then[/COLOR]
         ActiveSheet.Tab.ColorIndex = 4 [COLOR=green]'green[/COLOR]
      [COLOR=darkblue]Else[/COLOR]
         ActiveSheet.Tab.ColorIndex = xlNone
      [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
End [COLOR=darkblue]Sub[/COLOR]

 

[kendel]

Hiya bertie, thanks again for helping me. I can't get this to work. I opened the VB and inserted a module into several sheets and added the code, but nothing happens.

Here's what I'm doing:
Each worksheet records the performance data of 10 machine operators for a day. They use a time clock to record the time it takes them to complete various tasks. They use a worksheet to record the data; there are several mathematical operations the operators must perform on the worksheet, the most challenging being the addition and subtraction of time. The worksheet is then given to the data entry person who then records the data in the workbook. The data entry person just records the days totals as calculated by the operators, has no time to check the 20 or so calculations if there's an error.
The operators are listed in A2:A11.
The Column headers are:
Col B = Available time, typically 630 minutes
Col C = Set up time, how much time is spent preparing a job
Col D = Print time, how much time it takes to finish the project
Col E = downtime, time lost due to circumstances beyond the control of the operator.
Col F =sum(B2-E2), the actual time available to the operator to perform all the tasks required in the day.
Col G =sum(C2+D2)/F2, this shows how efficient they were in the use of their time, the goal is 85%, and this is where the errors in their calculations become evident, they get greater than 100%.
There are another 8 or so columns of unrelated information and so in column P I have this formula: =IF(G2>100%,"1","") which I hope will trigger the sheet tab color change.
Could the formula in each cell of Col P be interfering with the macro?

Hey I hope this is as much fun for you as it is for me
thanks again, Kendel

 

[bertie]

Rather then using a change event try the code below. This would go in the ThisWorkbook module.

The code loops through each worksheet and performs a CountIF over the given range. If the countif vaue is greater than zero this triggers the change tab colour.

The procedure can be called from the Tools => Macro => Macros menu.

[COLOR=darkblue]Option[/COLOR] [COLOR=darkblue]Explicit[/COLOR]
 
[COLOR=darkblue]Sub[/COLOR] Check()
   [COLOR=darkblue]Dim[/COLOR] ws [COLOR=darkblue]As[/COLOR] Worksheet
   [COLOR=darkblue]Dim[/COLOR] mycheck [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Integer[/COLOR]
 
   [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] ws [COLOR=darkblue]In[/COLOR] Worksheets
      [COLOR=green]'check the entries[/COLOR]
      [COLOR=darkblue]With[/COLOR] ws
         [COLOR=green]'================[/COLOR]
         [COLOR=green]'EDIT THE RANGE[/COLOR]
         [COLOR=green]'================[/COLOR]
         mycheck = Application.WorksheetFunction.CountIf(.Range("P2:P11"), ">0")
         [COLOR=darkblue]If[/COLOR] mycheck > 0 [COLOR=darkblue]Then[/COLOR]
            .Tab.ColorIndex = 4  [COLOR=green]'green[/COLOR]
         [COLOR=darkblue]Else[/COLOR]
            .Tab.ColorIndex = xlNone
         [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
      [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
   [COLOR=darkblue]Next[/COLOR] ws
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

 

[kendel]

Hi bertie, thanks for the help, that last code worked except for the need to actually run the macro. I'll start a new thread on how to run the macro automatically when the workbook is opened.
Oh and I changed the xlNone so that the tabs have a default color to start with.

 

[bertie]

Enclose the code within a workbook open event, and place it in the ThisWorkbook module.

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] Workbook_Open()
   [COLOR=darkblue]Dim[/COLOR] ws [COLOR=darkblue]As[/COLOR] Worksheet
   [COLOR=darkblue]Dim[/COLOR] mycheck [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Integer[/COLOR]
 
   [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] ws [COLOR=darkblue]In[/COLOR] Worksheets
      [COLOR=green]'check the entries[/COLOR]
      [COLOR=darkblue]With[/COLOR] ws
         [COLOR=green]'================[/COLOR]
         [COLOR=green]'EDIT THE RANGE[/COLOR]
         [COLOR=green]'================[/COLOR]
         mycheck = Application.WorksheetFunction.CountIf(.Range("P2:P11"), ">0")
         [COLOR=darkblue]If[/COLOR] mycheck > 0 [COLOR=darkblue]Then[/COLOR]
            .Tab.ColorIndex = 4  [COLOR=green]'green[/COLOR]
         [COLOR=darkblue]Else[/COLOR]
            .Tab.ColorIndex = xlNone
         [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
      [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
   [COLOR=darkblue]Next[/COLOR] ws
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

 

[kendel]

Hi bertie, still with me Great!
something odd happened with that workbook open code, it just stripped the color from all the tabs when the wb was opened. I even changed the color indexs. Any thoughts as to why the same code worked differently??

 

[bertie]

The only reason I can think of it not working is if you have changed the values in the cells. Try using an AND statement to retain the colour of tabs, sonething like:

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] Workbook_Open()
   [COLOR=darkblue]Dim[/COLOR] ws [COLOR=darkblue]As[/COLOR] Worksheet
   [COLOR=darkblue]Dim[/COLOR] mycheck [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Integer[/COLOR]
 
   [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] ws [COLOR=darkblue]In[/COLOR] Worksheets
      [COLOR=green]'check the entries[/COLOR]
      [COLOR=darkblue]With[/COLOR] ws
         [COLOR=green]'================[/COLOR]
         [COLOR=green]'EDIT THE RANGE[/COLOR]
         [COLOR=green]'================[/COLOR]
         mycheck = Application.WorksheetFunction.CountIf(.Range("P2:P11"), ">0")
        [COLOR=red] [B]If mycheck > 0 And .Tab.ColorIndex <> 4[/B][/COLOR] [COLOR=darkblue]Then[/COLOR]
            .Tab.ColorIndex = 4  [COLOR=green]'green[/COLOR]
         [COLOR=darkblue]Else[/COLOR]
            .Tab.ColorIndex = xlNone
         [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
      [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
   [COLOR=darkblue]Next[/COLOR] ws
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]