Esxcel conditional formatting

[Al]

Does anyone know of a way to conditionally format the color of a worksheet
tab based on certain conditions in one or more cells? I'm (my company ) is
using excel 2002

 

[Shane Devenshire]

Hi,

Here is the basic idea, but you will need to modify it to meet your
conditions and cell addresses.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim isect As Range
Set isect = Application.Intersect(Target, Range("A1"))
If Not isect Is Nothing Then
'your code for testing conditions in

ActiveWorkbook.Sheets("Sheet2").Tab.ColorIndex = 42
End If
End Sub

If this helps, please click the Yes button.

Cheers,
Shane Devenshire

 

[Al]

Shane,
Thanks this was a good starting point. I found another snippet of yours in
the google excel group that gets even closer.

Private Sub Worksheet_Change(ByVal Target As Range)
Set isect = Application.Intersect(Target, [MyRange])
If Not isect Is Nothing Then
If Target = "" Then
Target.Interior.ColorIndex = 6
Else
Target.Font.ColorIndex = 3
End If
End If
End Sub

Using this example I modified your original slightly to

Private Sub Worksheet_Change(ByVal Target As Range)
Dim isect As Range
Set isect = Application.Intersect(Target, Range("A1:E10"))
If Not isect Is Nothing Then
'your code for testing conditions in
If Target < "1" Then
ActiveWorkbook.Sheets("Sheet1").Tab.ColorIndex = 3
Else
ActiveWorkbook.Sheets("Sheet1").Tab.ColorIndex = 4
End If
End If
End Sub

What I am trying to do is, in my range of cells A1 through E10
if the number in any cell changes to 0 I want the active tab color to
change to red. only when all the cells in the range are equal to or greater
than 1 do I want the tab to change to green. The only exception is that I
also want empty cells to cause the active tab to be green. Another way to put
it is if any one or multiple cell(s) are 0 make the tab red and keep it red
until all the cells in the range contain either a value greater than zero or
are empty.,

Thanks for your help,