Delete values colored cells

M

marc747

Hi,
I am trying to make a macro that when I run it, it Deletes the values
of the cells that are yellow in interior color,
I appreciate for any help! Thanks!
 
M

marcus

Hi Mark

Assuming your data is in Column A and starts in Row 2, this should
sort you out. Change column to suit if this is not the case.

Take care

Marcus

Sub DeleteYellow()

Dim Lw As Long
Application.ScreenUpdating = False
Lw = Range("A" & Rows.Count).End(xlUp).Row

'Change 2 to appropriate number
For i = Lw To 2 Step -1
If Range("A" & i).Interior.ColorIndex = 6 Then
Range("A" & i).EntireRow.Delete
End If
Next i

End Sub
 
P

Peter T

Have a go with this (note it also clears the yellow in cells)

Sub DelYellows()
Dim lClr As Long
Dim rng As Range, c As Range

If TypeName(Selection) <> "Range" Then
MsgBox "Select cells"
Exit Sub
End If

Set rng = Intersect(ActiveSheet.UsedRange, Selection)

If rng Is Nothing Then
' selection outside the usedrange
Exit Sub
End If

If rng.Count > 100000 Then
If MsgBox(rng.Count & " cells to process, might take a while", _
vbOKCancel) <> vbOK Then
Exit Sub
End If
End If

For Each c In rng
If c.Interior.Color = vbYellow Then
c.Interior.ColorIndex = xlNone
c.ClearContents
End If
Next

End Sub

Regards,
Peter T
 
M

marcus

Hi Mark

I read your post incorrectly. This removes the values of those cells
which are yellow in Column A. Change the column to suit.

Marcus

Sub DeleteYellow()

Dim Lw As Long
Application.ScreenUpdating = False
Lw = Range("A" & Rows.Count).End(xlUp).Row

For i = Lw To 2 Step -1
If Range("A" & i).Interior.ColorIndex = 6 Then
Range("A" & i).Value = ""
End If
Next i

End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top