How To Delete Colored Cells In Excel Vba

by

To delete colored cells in Excel using VBA, you can loop through each cell in the range, check its fill color, and delete the cell if its color matches the desired color. Here’s an example code that demonstrates this: 

Sub DeleteColoredCells()
    Dim rng As Range
    Dim cell As Range
    Dim colorToMatch As Long
    
    ' Set the desired color to match
    colorToMatch = RGB(255, 0, 0) ' Red color
    
    ' Set the range where you want to delete colored cells
    Set rng = Range("A1:D10")
    
    ' Loop through each cell in the range
    For Each cell In rng
        ' Check if the cell's fill color matches the desired color
        If cell.Interior.Color = colorToMatch Then
            ' Delete the cell
            cell.Delete Shift:=xlShiftUp
        End If
    Next cell
End Sub

In this example, the code deletes cells in the range A1:D10 that have a fill color matching the RGB value for red (255, 0, 0). You can modify the colorToMatch variable to match your desired color. The “cell.Delete Shift:=xlShiftUp” statement deletes the cell and shifts the cells above it up to fill the empty space.

Make sure to update the range and colorToMatch variables according to your specific requirements. Also, it’s always a good practice to test the code on a sample data before running it on your actual data.

Leave a comment