Removing cells that are a certain colour.

[BenNV]

Good Morning All,

I am trying to remove a line if a cell is a certain colour (in this case, yellow). This is the code I thought I'd use, but it doesn't seem to like it.

Set JerWB = Workbooks("Test.xls").Worksheets("use")

rr = 1


If (JerWB.Cells(rr, 1).ColorIndex = 6) Then
JerWB.Cells(rr, 1).EntireRow.Delete Shift:=xlShiftUp
Else
rr = rr + 1
End If

[Nimrod]

Are you sure you only want to increment your loop counter when there is no yellow color ?

You probably want to have rr=rr+1 outside of the if statement

[Nimrod]

Set JerWB = Workbooks("Test.xls").Worksheets("use") 

do

 rr = rr +1
 If (JerWB.Cells(rr, 1).ColorIndex = 6) Then 
  JerWB.Cells(rr, 1).EntireRow.Delete    Shift:=xlShiftUp 
 End If
loop until(JerWB.Cells(rr, 1).value = ""

_________________
NOTE: Testing performed on Win2K utilizing Office 2000. Solutions may need tweaking for other versions.

[ This Message was edited by: Nimrod on 2002-05-13 01:58 ]

[BenNV]

No luck with that Nimrod.

Run Time error 438:
Object doesn't support this property or method.

[Tom Schreiner]

Hi Ben
This appears to work...

Sub DeleteYellowRows()

Dim Cntr

For Cntr = 1 To ActiveSheet.UsedRange.Columns(1).Rows.Count

    If Cells(Cntr, 1).Interior.ColorIndex = 6 Then

      Rows(Cntr).Delete

      Cntr = Cntr - 1

    End If

Next

End Sub

Tom

P.S. If the Font color is what you are after then replace the property "Interior" with the object "Font"

[ This Message was edited by: TsTom on 2002-05-13 03:14 ]