Change Tab Colour using VBA

[ASTCG89]

Good Evening from not so sunny UK.

I have a question with regards to using VBA to automatically change the Tab Colours.

I'm an accountant and have several tabs on an excel spreadsheet.
Each Tab represents a line on my Trial Balance. (TB)

I would like my spreadsheet to conditionally format Tabs Based on:
1. Where a TB Line is Zero I do not have to look at that particular tab. (Tab Goes Grey)
2. Where the TB Line has a value that is different to what is in the tab. (Tab Goes YELLOW)
3.Where the TB Line Agrees to what is in the TAB (Tab Changes to GREEN)

I have managed to copy and paste other peoples codes to get the cells to change colour. However they only changed after you visited the particular cell in the tab and it could not read formulas

Each Tab will look up the TB line balance that I dump into sheet 1and subtract that balance from the total of the items in the tab. so that Cell that shows the Difference is essentially what I want the Macro to conditionally format the TABS with.


Hope this makes sense?

Thanks

Adrian

 

[Scott T]

The lines below chnge the tab color. You will need to change the sheet name to match. You will need to write the if statements based on your data.

    ActiveWorkbook.Sheets("Sheet4").Tab.ColorIndex = 16 'grey

    ActiveWorkbook.Sheets("Sheet4").Tab.ColorIndex = 6 'yellow

    ActiveWorkbook.Sheets("Sheet4").Tab.ColorIndex = 4 'green

 

[svanslyck]

I've tried tons of the online examples but none work.

Sub CreateSheets()
        Dim ws1 As Worksheet
        Set ws1 = ThisWorkbook.Worksheets("Sheet Template")
        
        ws1.Copy ThisWorkbook.Sheets("Summary")
            ActiveSheet.Name = "Green, Sam" '__________
            'Sheets("Green, Sam").Tab.ColorIndex = 0
            'ActiveSheet.Sheets("Green, Sam").Tab.ColorIndex = 0
            'ActiveSheet.Tab.ColorIndex = 0
            'ActiveWorkbook.Sheets("Green, Sam").Tab.ColorIndex = 0
            
            
        ws1.Copy ThisWorkbook.Sheets("Summary")
            ActiveSheet.Name = "Smith, John" '________________
            Sheets("Smith, John").Tab.ColorIndex = 0
End Sub

 

[svanslyck]

(The commented lines did not work.)

 

[Fluff]

What colour are you trying to make tab?

 

[svanslyck]

Black. Misread info on mvps.org but with help I finally figured it out and this line worked:

ActiveSheet.Tab.ColorIndex = 1

 

[Fluff]

Glad you sorted it & thanks for the feedback.