tab.colorindex linked to cell color

[Guest]

I am running Excel 2003 SP2 and have been trying to figure this out for 2
days. I have reviewed all of he postings and cannot get this to work. I have
a 4 sheet workbook. In cell A16 of the 3rd worksheet I have the formula
=IF(AND F10>=2,F11>=2,F12>=2),"COMPLETED", "NOT COMPLETED") I also have
conditional formatting applied to this cell so that when "COMPLETED" is
displayed, the cell color is #4 (green) and when "NOT COMPLETED" is
displayed, the cell color is #3 (red). I would like to have the worksheet tab
color mirror this cell color. That way, when I am viewing any other
worksheet, I can simply look at the tab for the 3rd worksheet and determine
by the tab color if it is COMPLETED or NOT COMPLETED. I would really like to
get this working as I have been told it can't be done. Thanks in advance for
any help.

 

[Guest]

Actually you are almost there. Paste the following in Worksheet code:

Private Sub Worksheet_Change(ByVal Target As Range)
Set r = Range("F10:F12")
If Intersect(Target, r) Is Nothing Then
Exit Sub
End If
v10 = Range("F10").Value
v11 = Range("F11").Value
v12 = Range("F12").Value
If v10 > 2 And v11 > 2 And v12 > 2 Then
ActiveSheet.Tab.ColorIndex = 50
Else
ActiveSheet.Tab.ColorIndex = 3
End If

End Sub

Whenever F10, F11, or F12 change, the macro tests if the tab should be red
or green. Just paste the code the the Worksheet code area of each sheet you
want to automate.

REMEMBER: worksheet code not a standard module.

 

[Guest]

Hi Gary's Student,

Thanks for the anwer. It worked as you said it would. I, however, neglected
to mention that the data in cells F10, F11, and F12 was imported from the
first worksheet. Apparently, the macro does not work if the data is not
manually entered. I managed to fix this by placing the macro in the first
sheet where the data is manually entered. I then changed the
ActiveSheet.Tab.ColorIndex to ActiveWorksheet.Sheets("Sheet3").Tab.ColorIndex
and everything worked like a champ. I could not have done it without you.

I really appreciate your help.

Mike