Removing cells that are a certain colour.

BenNV

Board Regular
Joined
Mar 27, 2002
Messages
160
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
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
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
 
Upvote 0
Code:
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
 
Upvote 0
No luck with that Nimrod.

Run Time error 438:
Object doesn't support this property or method.
 
Upvote 0
Hi Ben
This appears to work...<pre>

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</pre>

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
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,264
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top