Change tab color based on cell value

[ksaw]

Hello,

I am looking to have each tab in a workbook change based on the value of cell "AE1". There are 4 possible values of this cell, A, B, C, or D and the colors are below:

A = Red
B = Yellow
C = Blue
D = Green

I assume I may need to write a macro to do this, but I don't have alot of experience with this. Would anyone be able to help with this??

 

[MrKowz]

Welcome to the forums!

Is AE1 a cell with a formula, or is it a cell that you manually change?

 

[VoG]

Press ALT + F11 to open the Visual Basic Editor, in the Project window double click ThisWorkbook then paste in

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim iCol As Long
If Target.Address(False, False) = "AE1" Then
    Select Case Target.Value
        Case "A": iCol = 3
        Case "B": iCol = 6
        Case "C": iCol = 5
        Case "D": icol4
        Case Else: iCol = 2
    End Select
    Sh.tab.ColorIndex = iCol
End If
End Sub

Press ALT + Q to close the code window. Change AE1 to see the result.

 

[ksaw]

Thanks!

AE1 is a vlookup formula.

 

[VoG]

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
Dim iCol As Long
    Select Case Range("AE1").Value
        Case "A": iCol = 3
        Case "B": iCol = 6
        Case "C": iCol = 5
        Case "D": icol4
        Case Else: iCol = 2
    End Select
    Sh.tab.ColorIndex = iCol

End Sub

 

[MrKowz]

Try:

Private Sub Workbook_SheetCalculate(ByVal sh As Object)
    Select Case sh.Range("AE1").Value
        Case "A"
            sh.Tab.ColorIndex = 3
        Case "B"
            sh.Tab.ColorIndex = 6
        Case "C"
            sh.Tab.ColorIndex = 5
        Case "D"
            sh.Tab.ColorIndex = 4
        Case Else
            sh.Tab.ColorIndex = 2
    End Select
End If
End Sub

 

[rcdgreat]

Hi,

Yes, you will need to create a macro on this one. Hope this would help you in your scenario:

Assuming the sheet that your working on is named "Sheet1".

1. Open the Visual Basic Editor (Tools>Macro>Visual Basic Editor).
2. In the left side of the window, right click Sheet1 and select View Code.
3. In the Sheet1 Code dialog box you will see two dropdown buttons, on the left dropdown choose Worksheet and from the right dropdown choose Select.
4. Enter the Following lines of code and then that's it. Hope This works..

Select Case Range("AE1").Value
Case "A"
Sheets("Sheet1").Select
With ActiveWorkbook.Sheets("Sheet1").Tab
.Color = 255
.TintAndShade = 0
End With
Case "B"
Sheets("Sheet1").Select
With ActiveWorkbook.Sheets("Sheet1").Tab
.Color = 65535
.TintAndShade = 0
End With
Case "C"
Sheets("Sheet1").Select
With ActiveWorkbook.Sheets("Sheet1").Tab
.Color = 12611584
.TintAndShade = 0
End With
Case "D"
Sheets("Sheet1").Select
With ActiveWorkbook.Sheets("Sheet1").Tab
.Color = 5287936
.TintAndShade = 0
End With
End Select

 

[ksaw]

Wow, thanks everyone for the help! Now, risking sounding like an idiot....how to I close a thread?

 

[VoG]

how to I close a thread?

you can't on this board.