Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formatting in Excel
Hi Guys,
I have set up some conditional formatting in excel, and it colours cells depending on date formats i have set. However, does anyone know whether it is possible to also colour the tabs of each worksheet if one of the rules from conditional formatting is met. For example, if the cell turns red according to the rules i have set in conditional formatting i also want the tab to turn red- is this possible? Cheers, Rodney |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formatting in Excel
Hi,
There are ways of doing this directly i.e actualy testing the conditional format and you can see that he- http://www.xldynamic.com/source/xld.CFConditions.html However if your requirement is as simple as looking at one cell and colouring the sheet tab then here's another way. Say you have a simple conditional format that turns A1 red if it's 5. You can utilise the worksheet change event to check the cells value and colour the tab. Right click the worksheet tab, view code and paste this in. It simply checks the same condition the conditional format is testing for and colours the tab. Private Sub Worksheet_Change(ByVal Target As Range) If Cells(1, 1).Value 5 Then Sheets("Sheet1").Tab.ColorIndex = 3 Else Sheets("Sheet1").Tab.ColorIndex = -4142 End If End Sub Mike "Rodney" wrote: Hi Guys, I have set up some conditional formatting in excel, and it colours cells depending on date formats i have set. However, does anyone know whether it is possible to also colour the tabs of each worksheet if one of the rules from conditional formatting is met. For example, if the cell turns red according to the rules i have set in conditional formatting i also want the tab to turn red- is this possible? Cheers, Rodney |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formatting in Excel
Hi,
I should have added this is a long way from being bullet proof. If for example your cell changes value because of a change on another sheet then the recalculation of A1 doesn't call the sheet change event and it fails. Mike "Mike H" wrote: Hi, There are ways of doing this directly i.e actualy testing the conditional format and you can see that he- http://www.xldynamic.com/source/xld.CFConditions.html However if your requirement is as simple as looking at one cell and colouring the sheet tab then here's another way. Say you have a simple conditional format that turns A1 red if it's 5. You can utilise the worksheet change event to check the cells value and colour the tab. Right click the worksheet tab, view code and paste this in. It simply checks the same condition the conditional format is testing for and colours the tab. Private Sub Worksheet_Change(ByVal Target As Range) If Cells(1, 1).Value 5 Then Sheets("Sheet1").Tab.ColorIndex = 3 Else Sheets("Sheet1").Tab.ColorIndex = -4142 End If End Sub Mike "Rodney" wrote: Hi Guys, I have set up some conditional formatting in excel, and it colours cells depending on date formats i have set. However, does anyone know whether it is possible to also colour the tabs of each worksheet if one of the rules from conditional formatting is met. For example, if the cell turns red according to the rules i have set in conditional formatting i also want the tab to turn red- is this possible? Cheers, Rodney |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
"Excel encountered an error and had to remove some formatting toavoid corrupting the workbook. Please re-check your formatting carefully." | Excel Discussion (Misc queries) | |||
Formatting cells in a column with conditional formatting? | Excel Discussion (Misc queries) | |||
Saving Excel 2007 conditional formatting in Excel 2003 format | Excel Discussion (Misc queries) | |||
Protect Cell Formatting including Conditional Formatting | Excel Discussion (Misc queries) | |||
expanding custom formatting without removing existing cell formatting? | Excel Worksheet Functions |