macro for Tab colour change

[Mike]

This is what i have but the problem is when i type something in cell the Tab
changes colour this is good & when i clear cell i want Tab to go back to its
original colour is this possible

Private Sub Workbook_SheetChange(ByVal Sh As Object, _
ByVal Target As Range)
If Intersect(Target, Range("B300")) Is Nothing Then Exit Sub
ActiveSheet.Tab.ColorIndex = 15
End Sub

 

[Rik_UK]

You could try this...

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Intersect(Target, Range("B300")) Is Nothing Then
ActiveSheet.Tab.ColorIndex = 15
Else
ActiveSheet.Tab.ColorIndex = [put original colorindex here]
End if
End Sub

Hope it helps...

 

[Mike H]

Mike,

Try this

Private Sub Workbook_SheetChange(ByVal Sh As Object, _
ByVal Target As Range)
If Intersect(Target, Range("B300")) Is Nothing Then Exit Sub
Select Case Target.Value
Case Is = ""
ActiveSheet.Tab.Color = xlAutomatic
Case Else
ActiveSheet.Tab.Color = 15
End Select
End Sub

Mike

 

[Rik_UK]

An elementary error in my code, should have been:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Intersect(Target, Range("B300")) Is Nothing Then Exit Sub
If Target.Value <> "" Then
ActiveSheet.Tab.ColorIndex = 15
Else
ActiveSheet.Tab.ColorIndex = [original ColorIndex]
End If
End Sub

With thanks to Mike H's answer to make me realise this...

--
Kind regards

Rik

You could try this...

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Intersect(Target, Range("B300")) Is Nothing Then
ActiveSheet.Tab.ColorIndex = 15
Else
ActiveSheet.Tab.ColorIndex = [put original colorindex here]
End if
End Sub

Hope it helps...

--
Kind regards

Rik

This is what i have but the problem is when i type something in cell the Tab
changes colour this is good & when i clear cell i want Tab to go back to its
original colour is this possible

Private Sub Workbook_SheetChange(ByVal Sh As Object, _
ByVal Target As Range)
If Intersect(Target, Range("B300")) Is Nothing Then Exit Sub
ActiveSheet.Tab.ColorIndex = 15
End Sub