How can I change sheet tab color based on cell value in sheet?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I use an Excel spreadsheet form to track various landscaping projects. I
denote the status of the project by using colors assigned by conditional
formatting based on the value of a status drop-down listbox on the sheet,
which works great, but I'd also like the tab color of the sheet to match the
status the various status colors I've chosen. Is there a way to do this?
 
Hello SCAScot,

You can't change the color of the Worksheet tabs. The Tabs object class
doesn't have a BackColor property like most objects, and the other
obstacle is Excel doesn't expose the Worksheet Tab properties through
VBA.

Sincerely,
Leith Ross
 
Unless you are using Excel 2002 or late in which case you can. This
examle changes the tab color every time the value in Cell A1 changes.
This is worksheet event code. Right click the sheet tab, select view
code and paste the event in there. Change it to suit your needs:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count = 1 Then
If Target.Address = "$A$1" Then
Select Case Target.Value
Case 5
Me.Tab.ColorIndex = 36
Case 6
Me.Tab.ColorIndex = 35
Case Else
Me.Tab.ColorIndex = xlNone
End Select
End If
End If
End Sub

Hope this helps
Rowan
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Changing sheet name based on a cell in another sheet 1
Excel VBA to move cells to sheet2 based on selection in sheet1 0
Return cell(s) Conditional Formatting Status via a macro 3
Changing Cell Color based upon value on another worksheet 2
Capturing conditional Interior Color of cell 2
Color of sheet tabs question please 2
Lookup and Enter Value to Other Tab on Change in Cell 0
Button Color based on cell 12

Back
Top