Delete the row is not marked with color

[vinzent]

Hello to all
I have this code using in other file to delete the rows if they not have some color in column A, but I tried to use in other file with more columns and is not working; due I am novice on this,
I am not sure what is wrong on this code , the difference is only the color is now in column E, not A

Thanks for all.

Sub Deletenotcolors in column A()
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    'Part 1
    Sheets("Report").Select
    'Delete row 1
    Rows("1:1").Select
    Selection.Delete Shift:=xlUp
    'Deleted rows not colored
    Dim Cel As Range, Rng As Range, uRng As Range, c As Long, r As Long
    Set Rng = ActiveSheet.UsedRange
    Set uRng = Rng.Offset(Rng.Rows.Count).Resize(1, 1)
  
    For r = 2 To Rng.Rows.Count
        For c = 1 To Rng.Columns.Count
            Set Cel = Rng.Cells(r, c)
            If Not Cel.EntireRow.Interior.ColorIndex = 19 Then
                Set uRng = Union(uRng, Cel)
                Exit For
            End If
        Next c
    Next r
    uRng.EntireRow.Delete
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    
End Sub

 

[PeteWright]

This is the line where column E (the 5th column) is addressed

For c = 5 To Rng.Columns.Count

Below code should do the job

Sub DeleteNotColorsInColumnE()
   Application.ScreenUpdating = False
   Application.DisplayAlerts = False

   'Part 1
   Sheets("Report").Select
   'Delete row 1
   Rows("1:1").Select
   Selection.Delete Shift:=xlUp
   'Deleted rows not colored
   Dim Cel As Range, Rng As Range, uRng As Range, c As Long, r As Long
   Set Rng = ActiveSheet.UsedRange
   Set uRng = Rng.Offset(Rng.Rows.Count).Resize(1, 1)
  
   For r = 2 To Rng.Rows.Count
      For c = 5 To Rng.Columns.Count
         Set Cel = Rng.Cells(r, c)
         If Not Cel.EntireRow.Interior.ColorIndex = 19 Then
            Set uRng = Union(uRng, Cel)
            Exit For
         End If
      Next c
   Next r
   uRng.EntireRow.Delete
   Application.ScreenUpdating = True
   Application.DisplayAlerts = True
End Sub

 

[vinzent]

This is the line where column E (the 5th column) is addressed

For c = 5 To Rng.Columns.Count

Below code should do the job

Sub DeleteNotColorsInColumnE()
   Application.ScreenUpdating = False
   Application.DisplayAlerts = False

   'Part 1
   Sheets("Report").Select
   'Delete row 1
   Rows("1:1").Select
   Selection.Delete Shift:=xlUp
   'Deleted rows not colored
   Dim Cel As Range, Rng As Range, uRng As Range, c As Long, r As Long
   Set Rng = ActiveSheet.UsedRange
   Set uRng = Rng.Offset(Rng.Rows.Count).Resize(1, 1)
 
   For r = 2 To Rng.Rows.Count
      For c = 5 To Rng.Columns.Count
         Set Cel = Rng.Cells(r, c)
         If Not Cel.EntireRow.Interior.ColorIndex = 19 Then
            Set uRng = Union(uRng, Cel)
            Exit For
         End If
      Next c
   Next r
   uRng.EntireRow.Delete
   Application.ScreenUpdating = True
   Application.DisplayAlerts = True
End Sub

Thanks for reply, this is working for me!!