CODEWITHSUNDEEP
excelvba
Angel Picoloni
Angel Picoloni

Reputation: 15

VBA - Change font color of specific cell value based on a sheet tab color

I have a value in the cell V6 and i want it to change its color if the color of a specific sheet tab called "Test" is red (vbRed). I have tried a code but it doesn't seem to change the color of the text in the cell. I was wondering what is wrong and what could be done to fix it.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Test")
If ws.Tab.ColorIndex = vbRed Then
Range("v6").Font.Color = vbRed
End If
End Sub

Upvotes: 1

Views: 540

Answers (1)

Maya
Maya

Reputation: 134

One way to do this is to use a Boolean variable (I called my variable colortest) to test if the tab color is equal to vbRed or not.

this sample code turns the font color red if the tab is vbRed, and turns the font color black if the tab is any other color than vbRed.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim ws As Worksheet, colortest As Boolean
Set ws = ThisWorkbook.Sheets("Test")

colortest = Sheets("Test").Tab.Color = vbRed

If colortest = True Then
    Range("v6").Font.Color = vbRed
ElseIf colortest = False Then
    Range("v6").Font.Color = vbBlack
End If

End Sub

`

Note: I used Worksheet_SelectionChange so the code runs as soon as you click out of cell vs having to edit a cell as in Worksheet_Change

Upvotes: 1

Related Questions