changing tab color conditionally

L

ldd

I have a Workbook that contains several pages. Each page represents a
different style of product. When my customer sends in an order I place
the order quantity in a certain named cell on the corresponding
worksheet. Is there a way that I can format the tab to change color if
the quantity of the named cell is greater than 0?

Thanks in advance.
 
M

MartinShort

You will need to write a small amount of code as follows:


Code:
--------------------
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Range("A1") > 0 Then
ActiveWorkbook.Sheets("Sheet1").Tab.ColorIndex = 50 ' Changes tab colour colour to green
Else
ActiveWorkbook.Sheets("Sheet1").Tab.ColorIndex = 2 'changes tab colour back to white
End If
End Sub
--------------------

Needless to say, replace Range("A1") with whatever you want.
Also the TabColorIndex is your call too.

Place it in the ThisWorkBook Module of visual basic project explorer.
I'm not sure what event you would want to attach it to. Open? Sheet
Change? Sheet Deactivate? I'm sure you'll choose the best one for
your needs.

Let us know if you need any extra help
 
Top