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

    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!