CODEWITHSUNDEEP
excelvba
Rod
Rod

Reputation: 151

How to change the color of a tab based on cell value on that sheet

I have several sheets in workbook. Each sheet has a date in cell U2. I would like to have all of the cells with a weekday value in U2 to have a tab color of green, and the tabs of the worksheets which have a weekend value in U2 to be yellow. I have found how to change the color of the tab, but don't know how to specifically tell it "Sheet4" tab color green. I am looking for the code to look like:

For each sht in Thisworkbook.worksheets
  If format(sht.Renge("U2"),"DDD") = "Saturday" _
    or format(sht.Renge("U2"),"DDD") = "Sunday" then
    sht.Tab.ColorIndex = "yellow"
  else
    sht.Tab.ColorIndex = "blue"
  end if
Next

Here is the code I have been working with:

Sub sbColorAllSheetTab()
    'Declaration
    Dim iCntr, sht As Worksheet

On Error GoTo ErrorHandler

    'Holds the colorIndex number
    iCntr = 2

    'looping throgh the all the sheets of the workbook
    For Each sht In ThisWorkbook.Worksheets

        'Debug.Print Format(sht.Range("U2"), "DDD")  'Tried to check value on sheet - failed
        iCntr = iCntr + 1

        'Applying the colors to Sheet tabs - works
        sht.Tab.ColorIndex = 10 'iCntr

        'Tried to print the value, but didn't work'
        'If I can confirm it sees the correct value in the sheet I can interrogate the value
        'Debug.Print sht.Name  '.Range("U2")
        Debug.Print sht.Range("U2") 'Failed
    Next

   Exit Sub
ErrorHandler:
   ' Error handling code
   Beep
   Resume Next
End Sub

Thanks

Upvotes: 1

Views: 2371

Answers (1)

nfloria
nfloria

Reputation: 127

In the spirit of your request ("I am looking for the code to look like:"), your code was very close.

I only changed:

  1. Your date format (from "DDD" to "DDDD")
  2. A misspelling of Range
  3. Substituted the integer for the tab color in place of your string

The closest thing to your code that I got to work is:

For Each sht In ThisWorkbook.Worksheets
  If Format(sht.Range("U2"), "DDDD") = "Saturday" _
    Or Format(sht.Range("U2"), "DDDD") = "Sunday" Then
    sht.Tab.ColorIndex = 6 'yellow
  Else
    sht.Tab.ColorIndex = 5 'blue
  End If
Next

Upvotes: 1

Related Questions