I've been trying to find a solution to this for a couple months, but have not been successful. I'm new to vba only taken it up on a self learn basis 4 mnths ago.
I would like to change the color of a sheet tab based on the value of a cell (that has a formula in it) in a different sheet, but same workbook. The Different Sheet is the Master sheet "Test Summaries", and the cell I'd like to refer to is different for each sheet.
What I need:
- Tab "Microscopy" to change either color to red/orange/green based on the formula in Sheet "Test Summaries" cell"C2" (in this case Red)
- Tab "Culture" to change either color to red/orange/green based on the formula in Sheet "Test Summaries" cell "C3" (in this case Red)
- ...and so on Workbook with Tabs named for each test in Master sheet and referencing cell showing formula
In the picture you'll see that I've got a formula that tells me what color the tab should be based on specific testing criteria. I've tried the following code, but it won't change the color of the tab unless the cell contains text and the macro only allows me to refer to one cell.
I got this off the internet and modified it to what I need, but when I put it into the Sheets Code (Right click on sheet -> View Code) nothing happens.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyVal$
MyVal = ActiveWorkbook.Sheets("Test Summaries").Range("C2")
With ActiveSheet.Tab
Select Case MyVal
Case "RED"
.Color = vbRed
Case "GREEN"
.Color = vbGreen
Case "AMBER"
.ColorIndex = 45
End Select
End With
End Sub
My only thought is that it is trying to read text and sees a formula instead.
I'd really appreciate help on this as it would save me an hours of work each morning, as I manually change over 50 Tabs.
