Hello,

I have a large populated spreadsheet. Certain random cells of this
spreadsheet have a background color of red. Is there anyway to go down each
row of the spreadsheet and filter out all the rows that have a cell with the
background color of red? The colored cells are located in different columns.
I need to filter/sort and get rid of the rows that have no color at all.
(that is - the colored cells are of importance)

I tried to seach for similar problems on here but found none. Any help
would be appreciated.

Thanks,
Terrel

Sub DeleteRows()
Dim lastrow as long, lastColumn as Long
Dim i as Long, j as Long
Dim bColored as Boolean
LastRow = 500
LastColumn = 26
for i = lastrow to 2 step -1
bcolored = False
for j = 1 to lastcolumn
if cells(i,j).Interior.ColorIndex < xlNone then
bcolored = True
exit for
end if
Next
if not bColored then
rows(i).Delete
End if
Next
End if

Set values for LastRow and LastColumn
--
Regards,
Tom Ogilvy


"LoboNetwork" wrote in message
...
Hello,

I have a large populated spreadsheet. Certain random cells of this
spreadsheet have a background color of red. Is there anyway to go down
each
row of the spreadsheet and filter out all the rows that have a cell with
the
background color of red? The colored cells are located in different
columns.
I need to filter/sort and get rid of the rows that have no color at all.
(that is - the colored cells are of importance)

I tried to seach for similar problems on here but found none. Any help
would be appreciated.

Thanks,
Terrel

Thanks!
This is good.. But What if I dont want to delete the uncolored ones... hmm

"Tom Ogilvy" wrote:

Sub DeleteRows()
Dim lastrow as long, lastColumn as Long
Dim i as Long, j as Long
Dim bColored as Boolean
LastRow = 500
LastColumn = 26
for i = lastrow to 2 step -1
bcolored = False
for j = 1 to lastcolumn
if cells(i,j).Interior.ColorIndex < xlNone then
bcolored = True
exit for
end if
Next
if not bColored then
rows(i).Delete
End if
Next
End if

Set values for LastRow and LastColumn
--
Regards,
Tom Ogilvy


"LoboNetwork" wrote in message
...
Hello,

I have a large populated spreadsheet. Certain random cells of this
spreadsheet have a background color of red. Is there anyway to go down
each
row of the spreadsheet and filter out all the rows that have a cell with
the
background color of red? The colored cells are located in different
columns.
I need to filter/sort and get rid of the rows that have no color at all.
(that is - the colored cells are of importance)

I tried to seach for similar problems on here but found none. Any help
would be appreciated.

Thanks,
Terrel

Sub DeleteRows()
Dim lastrow as long, lastColumn as Long
Dim i as Long, j as Long
Dim bColored as Boolean
LastRow = 500
LastColumn = 26
Rows.Hidden = False
for i = lastrow to 2 step -1
bcolored = False
for j = 1 to lastcolumn
if cells(i,j).Interior.ColorIndex < xlNone then
bcolored = True
exit for
end if
Next
if not bColored then
rows(i).Hidden = True
End if
Next
End if

Hides the uncolored rows.

--
Regards,
Tom Ogilvy


"LoboNetwork" wrote in message
...
Thanks!
This is good.. But What if I dont want to delete the uncolored ones... hmm

"Tom Ogilvy" wrote:

Sub DeleteRows()
Dim lastrow as long, lastColumn as Long
Dim i as Long, j as Long
Dim bColored as Boolean
LastRow = 500
LastColumn = 26
for i = lastrow to 2 step -1
bcolored = False
for j = 1 to lastcolumn
if cells(i,j).Interior.ColorIndex < xlNone then
bcolored = True
exit for
end if
Next
if not bColored then
rows(i).Delete
End if
Next
End if

Set values for LastRow and LastColumn
--
Regards,
Tom Ogilvy


"LoboNetwork" wrote in message
...
Hello,

I have a large populated spreadsheet. Certain random cells of this
spreadsheet have a background color of red. Is there anyway to go
down
each
row of the spreadsheet and filter out all the rows that have a cell
with
the
background color of red? The colored cells are located in different
columns.
I need to filter/sort and get rid of the rows that have no color at
all.
(that is - the colored cells are of importance)

I tried to seach for similar problems on here but found none. Any
help
would be appreciated.

Thanks,
Terrel

I can work around the not-deleting by making a copy of the sheet and then
running the code on it.

The code you provided was of great help. Thank you again.

"Tom Ogilvy" wrote:

Sub DeleteRows()
Dim lastrow as long, lastColumn as Long
Dim i as Long, j as Long
Dim bColored as Boolean
LastRow = 500
LastColumn = 26
for i = lastrow to 2 step -1
bcolored = False
for j = 1 to lastcolumn
if cells(i,j).Interior.ColorIndex < xlNone then
bcolored = True
exit for
end if
Next
if not bColored then
rows(i).Delete
End if
Next
End if

Set values for LastRow and LastColumn
--
Regards,
Tom Ogilvy


"LoboNetwork" wrote in message
...
Hello,

I have a large populated spreadsheet. Certain random cells of this
spreadsheet have a background color of red. Is there anyway to go down
each
row of the spreadsheet and filter out all the rows that have a cell with
the
background color of red? The colored cells are located in different
columns.
I need to filter/sort and get rid of the rows that have no color at all.
(that is - the colored cells are of importance)

I tried to seach for similar problems on here but found none. Any help
would be appreciated.

Thanks,
Terrel

Hi Terrel,
you can use user defined function like this.

Function tellmecolor(colorcell As Range) As Integer
tellmecolor = colorcell.Interior.ColorIndex
End Function

to get the colorindexes of cells and then use filter on the colorindex
numbers.

Thanks.

"Roman" wrote:

Hi Terrel,
you can use user defined function like this.

Function tellmecolor(colorcell As Range) As Integer
tellmecolor = colorcell.Interior.ColorIndex
End Function

to get the colorindexes of cells and then use filter on the colorindex
numbers.

The data I wish to filter exists in a single column (e4:e900) of two
differing colors. I have used the user defined function:

Function tellmecolor(colorcell As Range) As Integer
tellmecolor = colorcell.Interior.ColorIndex
End Function

to define the values of my interior cell colors thus:
red = 3
green = 10

Now I can't figure out how to use the user defined function to place an
integer in each cell within the (e4:e900) range so I can filter out the 10's.

The user defined function as written only places a single integer in the
cell selected. Any help would be greatly appreciated.

haznavy

"LoboNetwork" wrote:

Hello,

I have a large populated spreadsheet. Certain random cells of this
spreadsheet have a background color of red. Is there anyway to go down each
row of the spreadsheet and filter out all the rows that have a cell with the
background color of red? The colored cells are located in different columns.
I need to filter/sort and get rid of the rows that have no color at all.
(that is - the colored cells are of importance)

I tried to seach for similar problems on here but found none. Any help
would be appreciated.

Thanks,
Terrel

Insert a new column F.

Select F4:F900
Type this:
=tellmecolor(e4)
and hit ctrl-enter to fill the selection with the formula (excel will adjust the
row number).

haznavy wrote:

The data I wish to filter exists in a single column (e4:e900) of two
differing colors. I have used the user defined function:

Function tellmecolor(colorcell As Range) As Integer
tellmecolor = colorcell.Interior.ColorIndex
End Function

to define the values of my interior cell colors thus:
red = 3
green = 10

Now I can't figure out how to use the user defined function to place an
integer in each cell within the (e4:e900) range so I can filter out the 10's.

The user defined function as written only places a single integer in the
cell selected. Any help would be greatly appreciated.

haznavy

"LoboNetwork" wrote:

Hello,

I have a large populated spreadsheet. Certain random cells of this
spreadsheet have a background color of red. Is there anyway to go down each
row of the spreadsheet and filter out all the rows that have a cell with the
background color of red? The colored cells are located in different columns.
I need to filter/sort and get rid of the rows that have no color at all.
(that is - the colored cells are of importance)

I tried to seach for similar problems on here but found none. Any help
would be appreciated.

Thanks,
Terrel

--

Dave Peterson

The data I wish to filter exists in a single column (e4:e900) of two
differing colors. I have used the user defined function:

Function tellmecolor(colorcell As Range) As Integer
tellmecolor = colorcell.Interior.ColorIndex
End Function

to define the values of my interior cell colors thus:
red = 3
green = 10

Now I can't figure out how to use the user defined function to place an
integer in each cell within the (e4:e900) range so I can filter out the
10's.

Dave gave you a Macro-As-Formula solution, but since you posted in the
excel.programming newsgroup, the possibility exists that you were looking
for a single function call to do all the work (for example, in response to
the click of a CommandButton). This modification to your function will do
that...

Function TellMeColor(ColorCells As Range) As Integer
Dim R As Range
For Each R In ColorCells
R.Value = R.Interior.ColorIndex
Next
End Function

If you go with the CommandButton activation method, here is what its Click
event would look like...

Private Sub CommandButton1_Click()
TellMeColor Range("E4:E900")
End Sub

for the fixed range you specified. If you wanted the function to operate on
a user defined selection, the Click event would look like this...

Private Sub CommandButton1_Click()
TellMeColor Selection
End Sub

Just highlight the cells of interest and click the button.

Rick

MS 2007 allows you to filter by cell color, but it's not working correctly.
When I filter on a selected color the system doesn't filter all of the cells
with the color and it displays some cells with a totally different color.
Any ideas?

Hi smierau

If you want send me a workbook with this problem private and
I will create a bug report if I can reproduce it

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm




"smierau" wrote in message ...
MS 2007 allows you to filter by cell color, but it's not working correctly.
When I filter on a selected color the system doesn't filter all of the cells
with the color and it displays some cells with a totally different color.
Any ideas?