Cell and tab color

[Slade]

Hi

I have a cell that change color only red(3) and green(4) and i want the sheet tab color to be the same. So i use this code but the if does not work. it seems the it does not pick up the cell color. The else works.

[COLOR=#000000][SIZE=2][FONT=Arial][B]Private Sub Worksheet_Change(ByVal Target As Range)
    If Range("D1").Interior.ColorIndex = 4 Then
         Me.Tab.ColorIndex = 4
    Else
         Me.Tab.ColorIndex = 3
    End If
End Sub[/B][/FONT][/SIZE][/COLOR]

 

[Leith Ross]

Hello Slade,

It may not be working because you are using a version of Excel prior to 2003. Earlier versions of Excel do not support the Tab object.

 

[villy]

Try this...

Private Sub Worksheet_Change(ByVal Target As Range)
    If Range("D1").Interior.ColorIndex = 4 Then
         Activesheet.Tab.ColorIndex = 4
    Else
         Activesheet.Tab.ColorIndex = 3
    End If
End Sub

 

[Slade]

I am on 2007

Did try but did not work aswell

[B][B][FONT=Arial]Private Sub Worksheet_Change(ByVal Target As Range)     
      If Range("D1").Interior.ColorIndex = 4 Then          
      Activesheet.Tab.ColorIndex = 4      
      Else          
      Activesheet.Tab.ColorIndex = 3     
End If 
End Sub[/FONT][/B][/B]

If i use values like this then it works fine.

[B][B][FONT=Arial]Private Sub Worksheet_Change(ByVal Target As Range)     
   If Range("D1") = 1 Then          
   Activesheet.Tab.ColorIndex = 4     
   Else          
   Activesheet.Tab.ColorIndex = 3     
   End If 
End Sub[/FONT][/B][/B]

can just not get it to work with color. It seems that it does not get the cell color from

Range("D1").Interior.ColorIndex

 

[Ruddles]

The reason it's not picking up the cell colour is because you're setting it by conditional formatting. Rather than try to determine the display colour of the cell, test the condition which is used to set the colour in the first place.

Example: if the cell is red because its value is less than zero, test its value being less than zero and then set the worksheet tab to red.

 

[villy]

Does it really matter the version of Excel because I am using 2003 and it works well with me.

 

[Slade]

The reason it's not picking up the cell colour is because you're setting it by conditional formatting. Rather than try to determine the display colour of the cell, test the condition which is used to set the colour in the first place.

Example: if the cell is red because its value is less than zero, test its value being less than zero and then set the worksheet tab to red.

This is correct yes i use conditional formatting. This is my condition formating for the cell.

=OR($F$7:$F$8="Not Received")

How will i go about to replace the other code to use the codition formatting to change the tab color then i dont even have to first do it to a cell and then to the tab.

 

[villy]

This one will substitute...

Private Sub Worksheet_Change(ByVal Target As Range)
    If Range("F7") = "Not Received" Or Range("F8") = "Not Received" Then
        Range("D1").Interior.ColorIndex = 4
        ActiveSheet.Tab.ColorIndex = 4
    Else
        ActiveSheet.Tab.ColorIndex = 3
    End If
End Sub

 

[Slade]

I have tried this but gives type mismatch. The reson why i did it like this is because some sheets got almost a range of 100 rows and to or for all of them will make the code to long.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Range("F7:F8") = "Not Recieved" Then
         Me.Tab.ColorIndex = 4
    Else
         Me.Tab.ColorIndex = 3
    End If
End Sub