Conditional Formatting a tab color

[Rhino V]

I know I'm missing something. I'm using Excel 2007 but a 2003 compatible
file for the versatility of my clients. I have a 7 sheet workbook that feeds
data into the first sheet. So as to know which sheets have been modified I'm
trying to use a sequence I found here. Under [View Code] of my tab called
"Replacements" I inserted

Sub Tab_Color()
If Sheets("Replacement").Range("E44").Value > 0 Then
Sheets("Replacement").Tab.ColorIndex = 4
Else
Sheets("Replacement").Tab.ColorIndex = -4142
End If
End Sub

where E44 has a formula for the whole sheet and is the cell that will
determine if the tab should change color or not. I just want to visually see
or be clued "if there's something there" while I'm on any other tab. And for
it to default back if no calculations are done on that sheet.

Using the above sequence doesn't do anything to the tab color when E44
becomes greater than 0 in its calculation. Please help...

 

[Jacob Skaria]

From workbook launch VBE using Alt+F11. From the treeview on the left right
click VBAProject>(workbookname)>'This Workbook' and hit View Code...On the
code pane paste the below code....

--Assume Replacement!E44 is having a formula...which is referred to the
other sheets...

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Sheets("Replacement").Range("E44").Value > 0 Then
Sheets("Replacement").Tab.ColorIndex = 4
Else
Sheets("Replacement").Tab.ColorIndex = -4142
End If
End Sub

If this post helps click Yes

 

[Patrick Molloy]

the idea is sound enough. But how will the code be forced? #
You're on some other sheet, so a sheet change or such, probably isn't
needed.
I suggest the the workbook's caluculate event would be good. in the IDE
select ThisWorkbook's code page and paste your code into :

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
If Sheets("Replacement").Range("E44").Value > 0 Then
Sheets("Replacement").Tab.ColorIndex = 4
Else
Sheets("Replacement").Tab.ColorIndex = -4142
End If
End Sub

to test this , i set E44 to point at at cell in sheet1. when is set the
sheet1 cell's value to a positive number , the tab went green.

The con could be that you'd either need calc mode on automatic, so set a
change event to calc the cell in the Replacement sheet.

 

[Rhino V]

It worked! Thank you so much. I was able to replicate "If" statement for
the other 4 tabs, and they behave flawlessly. Your skill and talent is much
appreciated.

From workbook launch VBE using Alt+F11. From the treeview on the left right
click VBAProject>(workbookname)>'This Workbook' and hit View Code...On the
code pane paste the below code....

--Assume Replacement!E44 is having a formula...which is referred to the
other sheets...

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Sheets("Replacement").Range("E44").Value > 0 Then
Sheets("Replacement").Tab.ColorIndex = 4
Else
Sheets("Replacement").Tab.ColorIndex = -4142
End If
End Sub

If this post helps click Yes
---------------
Jacob Skaria

I know I'm missing something. I'm using Excel 2007 but a 2003 compatible
file for the versatility of my clients. I have a 7 sheet workbook that feeds
data into the first sheet. So as to know which sheets have been modified I'm
trying to use a sequence I found here. Under [View Code] of my tab called
"Replacements" I inserted

Sub Tab_Color()
If Sheets("Replacement").Range("E44").Value > 0 Then
Sheets("Replacement").Tab.ColorIndex = 4
Else
Sheets("Replacement").Tab.ColorIndex = -4142
End If
End Sub

where E44 has a formula for the whole sheet and is the cell that will
determine if the tab should change color or not. I just want to visually see
or be clued "if there's something there" while I'm on any other tab. And for
it to default back if no calculations are done on that sheet.

Using the above sequence doesn't do anything to the tab color when E44
becomes greater than 0 in its calculation. Please help...