change tab color base on cell value

[Guest]

I would like to change the worksheet tab color to red if a date exist in cell
N13. I would like to set this up for the entire workbook, because new
worksheet can be added at any time.

Please keep in mind that I'm not a VB programmer.

Thanks for any input.
G. Wolfe

 

[Harald Staff]

Hi G

Locate the ThisWorkbook module in the VB editor (Alt F11 or similar) and
paste this in:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Address = "$N$13" Then
If IsDate(Target.Value) Then Sh.Tab.ColorIndex = 3
End If
End Sub

Assuming that the date is typed or pasted, and that removing red is not
needed when the date is deleted.

HTH. Best wishes Harald

 

[Guest]

Hi Harald,

That worked great. Thank You.

Upon further review. Would it be possible to have the tab color red if the
cell is empty, then remove the color once a date is placed in the cell.

Thanks again.
G. Wolfe

 

[Harald Staff]

Yes. The coloring technique is the very same, with just a different
colorindex, upon date entry. Problem is; who'll color the tabs red in the
first place ? Worksheets will be added, as you say.

Note also that colored sheet tabs came with Excel XP and code like this will
err on Excel 2000 and earlier. Which may not be a problem in your
environment, but just to make sure you know this potential problem...

HTH. Best wishes Harald