Formatting in Excel

[Rodney]

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

 

[Mike H]

Hi,

There are ways of doing this directly i.e actualy testing the conditional
format and you can see that here:-

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

 

[Mike H]

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