Colour Coding Worksheet Tabs

    I would appreciate some help with colour coding worksheet tabs.


    I have a workbook that contains a series of worksheets containing data, followed by a series of blank but formatted sheets. I need event coding that colours the sheet tab once I enter data onto a blank sheet so that I don't go mistakenly deleting a sheet that has data on it. (Can you tell what I'm trying to prevent a re-occurrence of ??)


    I'd like the colour in the RGB format so I can match it to the sheet tabs I've already coloured.

    Re: Colour Coding Worksheet Tabs


    If you try to delete a sheet with data on it Excel will give you a warning first that you have to manually respond to (unless you have DisplayAlerts set to False...)


    If you want to use an event though, try something like


    Code
    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)


If Sh.Name <> "master sheet" And Sh.Tab.Color = False Then
    Sh.Tab.Color = RGB(255, 0, 0) '// turn red
End If
    
End Sub


    NOTE: This code must be placed in the "ThisWorkbook" module in order to run.

    Re: Colour Coding Worksheet Tabs


    Great! That should do the trick.


    Yes, I do get the warning, but it's when I'm deleting a block of worksheets that I occasionally get in trouble by deleting 1 sheet too many. If I remember, I manually colour the tab once I've entered data onto a sheet; automating the process is a safety precaution.


    Thanks very much for your help.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login