macro for Tab colour change

  • Thread starter Thread starter Mike
  • Start date Start date
M

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
 
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,

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
 
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 said:
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 said:
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
Click to expand...
Click to expand...
 
Back
Top