Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting a tab color
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... |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting a tab color
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 "Rhino V" wrote: 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... |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting a tab color
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. "Jacob Skaria" wrote: 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 "Rhino V" wrote: 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... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
conditional formatting - color | Excel Discussion (Misc queries) | |||
conditional formatting color | Excel Worksheet Functions | |||
conditional formatting for tab color | Excel Worksheet Functions | |||
Conditional Formatting on Color | Excel Worksheet Functions | |||
Conditional color formatting entries have wild color. | Excel Discussion (Misc queries) |