macro for Tab colour change
 

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

macro for Tab colour change
 

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


"Mike" wrote:

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

macro for Tab colour change
 

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

"Mike" wrote:

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

macro for Tab colour change
 

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


"Rik_UK" wrote:

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


"Mike" wrote:

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