conditional formatting for tab color

[Rosanne]

Is there a way to conditionally color a tab based on the value of a cell in
the worksheet? I'm using Excel 2003 (but will be upgrading soon to 2007)

 

[krcowen]

Rosanne

I don't think you can directly link the tab color to a cell value;
but, you can change the tab color in an event triggered by a change in
the workbook or some worksheet event. It depends a lot on what is
going to be changing in your particular situation.

As an example, if you want a sheet tab to change color when you
deactivate a sheet (it won't make much difference while the sheet is
active since it will mostly be white then) you can have a code like:

Private Sub Worksheet_Deactivate()
Me.Tab.ColorIndex = Range("a1").Value
End Sub

This goes in the code associated with the particular worksheet. This
will change the sheet tab color index to the value in cell A1 whenever
you move to another sheet. Of course it will give an error if cell A1
contains an invalid color index; you can build the necessary error
checking in the code or in the worksheet validation as you see fit.

Good luck.

Ken
Norfolk, Va

 

[Gord Dibben]

You can do it with some type of event code.

What type of value...text or numeric?

How does the value change.......manually or formula driven?

Post a range of numbers or samples of text.


Gord Dibben MS Excel MVP