remove colored rows

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi
I have 2 columns that many of it's rows has colored ( colored cells in
first and second column is equal) , i want to remove colored rows.
Would you please help me?
regards
climate
 
Do you want to actually Delete the rows with colored cells, or do you just
want to remove the coloring from those rows but leave the data in them?

To just remove color and leave the information, choose the columns and use
Format | Cells and go to the [Patterns] tab (Excel 2003) and choose No Color.


If you want to actually delete the rows and data, it would help to know
which two columns we are talking about. Also, you mentioned "( colored cells
in first and second column is equal)" -- does this mean that the row should
be deleted ONLY when the cells on a row in those two columns are same
not-white color?
 
Hi
I have 2 columns that many of it's rows has colored ( colored cells in
first and second column is equal) , i want to remove colored rows.
Would you please help me?
regards
climate
Click to expand...

yes, you have to use macrofun
1, define a name, say "color" ---- insert-name-define,
"=get.cell(63,sheet1!A1), suppose the colored cells located in column
A
2, insert a column next to column A
3. enter a formula, say B1"=color",and drag down the formula
4. data filter, auto filter, customer "not equals to 0", at this time,
you can get all the colored rows
5. delete the rows,
6. done.

cheers.
ligang
 
Hummm, similar post earlier this morning...
I think this will give you what you want:
Sub DeleteAllUncolored()

Dim ClrRng As Range

Set ClrRng = Range("A1", Range("A5000").End(xlUp))
For Each cell In ClrRng
If cell.Interior.ColorIndex = 3 Then
cell.EntireRow.Delete
End If
Next cell
End Sub

This assumes that cells are colored red!! This site is great to see which
numbers correspond to which colors in Excel:
http://www.mvps.org/dmcritchie/excel/colors.htm

As always, be extremely careful when deleting date; try this macro on a
sample before you execute on your actual data.

Regards,
Ryan--
 
Hi thank's ryguy7272
1- My colored cells have different color(green,yellow,....).
2- your macro work's cell to cell, not for entire columns,please response to
these questions.
regards
 
Hi ligang
I didn't understand clearly, would you please describe in detailed,or write
complete code or macro.
regards
 
Try this.

Sub DeleteAll_colored()
Dim RowNdx As Long
Dim LastRow As Long
LastRow = ActiveSheet.UsedRange.Rows.Count
For RowNdx = LastRow To 1 Step -1
If Cells(RowNdx, "A").Interior.ColorIndex <> xlNone Then
Rows(RowNdx).Delete
End If
Next RowNdx
End Sub


Gord Dibben MS Excel MVP
 
Back
Top