How to delete sheets with a specific color tab using VBA?

To delete sheets with a specific color tab using VBA, you can loop through each worksheet in the workbook, check the tab color, and delete sheets that match the specified color. Below is a VBA example that demonstrates how to do this:

Sub DeleteSheetsWithSpecificTabColor()
    Dim ws As Worksheet
    Dim TabColor As Long
    Dim DeleteSheetNames As Collection
    Dim SheetName As Variant

    ' Set the color you want to check for (e.g., RGB(255, 0, 0) for red)
    TabColor = RGB(255, 0, 0)

    ' Initialize the collection to store sheet names to be deleted
    Set DeleteSheetNames = New Collection

    ' Loop through each worksheet in the workbook
    For Each ws In ThisWorkbook.Worksheets
        ' Check if the sheet tab color matches the specified color
        If ws.Tab.Color = TabColor Then
            ' Add the sheet name to the collection
            DeleteSheetNames.Add ws.Name
        End If
    Next ws

    ' Confirm deletion with the user
    If DeleteSheetNames.Count > 0 Then
        If MsgBox("Are you sure you want to delete sheets with the specified tab color?", vbYesNo + vbQuestion) = vbYes Then
            ' Delete the sheets listed in the collection
            For Each SheetName In DeleteSheetNames
                Application.DisplayAlerts = False
                ThisWorkbook.Sheets(SheetName).Delete
                Application.DisplayAlerts = True
            Next SheetName
            MsgBox "Sheets deleted successfully."
        Else
            MsgBox "Operation cancelled."
        End If
    Else
        MsgBox "No sheets found with the specified tab color."
    End If
End Sub

Instructions:

Open the Excel file where you want to run this VBA script.
Press `Alt + F11` to open the VBA editor.
Go to `Insert > Module` to create a new module.
Copy and paste the VBA code into the module.
Modify the `TabColor = RGB(255, 0, 0)` line to match the RGB color of the tab you want to delete. You can change the parameters in `RGB` to the color you need (e.g., `RGB(255, 255, 0)` for yellow).
Close the VBA editor and return to Excel.
Press `Alt + F8`, select `DeleteSheetsWithSpecificTabColor`, and click `Run`.

Notes:

The script will prompt you for confirmation before deleting any sheets.
Make sure to save your work before running the script, as sheet deletion cannot be undone.
The script uses a collection to store sheet names. This approach avoids modifying the collection of sheets while iterating, which can cause errors.

facebooklinkedin