Delete rows that contain highlighting

[Serafin54]

Hello,

I have this vba code that does the opposite of what i need. It currently deletes any rows that does not contain highlighting but need it do do the opposite. It had Colorindex = 19 which deleted all the rows not highlighted so I changed the index color to -4142 and it runs without error but doesn't do anything. hoping someone could point me to what I am missing.

Sub ShadedRow()
    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 = -4142 Then
                Set uRng = Union(uRng, cel)
                Exit For
            End If
        Next c
    Next R
    uRng.EntireRow.Delete
End Sub

 

[Micron]

I think -4241 represents the default setting for cell background and any number greater than zero would represent a colour. So maybe delete where the colorindex > 0 ? Step through your code first to be sure it executes as you expect and that the variables contain the values you expect.

 

[Serafin54]

I think -4241 represents the default setting for cell background and any number greater than zero would represent a colour. So maybe delete where the colorindex > 0 ? Step through your code first to be sure it executes as you expect and that the variables contain the values you expect.

Think I got it after a lot of trial and error.

If Not cel.Interior.ColorIndex = x1None

Thanks for the suggestion

 

[Micron]

That's an 'l' (lower case L) and not a 1?

 

[Serafin54]

Think I got it after a lot of trial and error.

If Not cel.Interior.ColorIndex = x1None

Thanks for the suggestion

mistake in my writing.

If Not cel.Interior.ColorIndex = -4142 Then

That's an 'l' (lower case L) and not a 1?

yes. sorry

 

[Serafin54]

mistake in my writing.

If Not cel.Interior.ColorIndex = -4142 Then

yes. sorry

If Not cel.Interior.ColorIndex = xlNone