Change The Tab Color

[wrangler]

How to Change The Tab Color Based On A Cell Date ( Thursday & Friday ) Green Color, Rest of the week blue.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

 

[bertie]

Hi and welcome to the forum.

For this to happen automatically you will need to use a Worksheet_Change event.

For example, consider the date is placed in cell A1. First we set the Change event to trigger only if A1 changes.

  [COLOR=green]'only trigger if cell A1 changes[/COLOR]
  [COLOR=darkblue]If[/COLOR] Target.Address <> "$A$1" [COLOR=darkblue]Then[/COLOR] [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]

We can the use the Weekday() function to extract the day from the date.
This returns an integer, 1-7. See the Excel Help file for how to change the week start day.

  [COLOR=green]'weekday() 1=Sunday, 2=Monday, etc[/COLOR]
  myDay = Weekday(CDate(Range("A1").Value))

And use a Select Case statement to control the tab colour:

  [COLOR=darkblue]Select[/COLOR] [COLOR=darkblue]Case[/COLOR] myDay
    [COLOR=darkblue]Case[/COLOR] vbThursday, vbFriday
      Me.Tab.ColorIndex = 4 [COLOR=green]'green[/COLOR]
    [COLOR=darkblue]Case[/COLOR] [COLOR=darkblue]Else[/COLOR]
      Me.Tab.ColorIndex = 5 [COLOR=green]'blue[/COLOR]
  [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Select[/COLOR]

To test the code below:
Open a new Excel workbook.
Press Alt+F11 to open the VBA editor.
Double click the Sheet1 module in the Project Window on the left hand side.
Copy and paste the code below.

Go to Sheet1 and place a date in cell A1.
Click on the sheet2 tab to see the full effect.

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] Worksheet_Change([COLOR=darkblue]ByVal[/COLOR] Target [COLOR=darkblue]As[/COLOR] Range)
  [COLOR=darkblue]Dim[/COLOR] myDay [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Integer[/COLOR]

  [COLOR=green]'only trigger if cell A1 changes[/COLOR]
  [COLOR=darkblue]If[/COLOR] Target.Address <> "$A$1" [COLOR=darkblue]Then[/COLOR] [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
  
  [COLOR=green]'weekday() 1=Sunday, 2=Monday, etc[/COLOR]
  myDay = Weekday(CDate(Range("A1").Value))

  [COLOR=darkblue]Select[/COLOR] [COLOR=darkblue]Case[/COLOR] myDay
    [COLOR=darkblue]Case[/COLOR] vbThursday, vbFriday
      Me.Tab.ColorIndex = 4 [COLOR=green]'green[/COLOR]
    [COLOR=darkblue]Case[/COLOR] [COLOR=darkblue]Else[/COLOR]
      Me.Tab.ColorIndex = 5 [COLOR=green]'blue[/COLOR]
  [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Select[/COLOR]
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

I hope this helps,
Bertie

 

[wrangler]

Thank you so much and I really appreciate your help Sir.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>