ExcelBanter - Filter but both color AND Text

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Filter but both color AND Text (https://www.excelbanter.com/excel-discussion-misc-queries/223966-filter-but-both-color-text.html)

Regalibar

Filter but both color AND Text

Hi All,

Is there a way to filter columns by both color and text? Some rows have
color some rows have text (Y/N). And I need to filter out all the gray cells
and the cells with "Y"

regards

Reginald

ExcelBanter AI

Answer: Filter but both color AND Text

Hi Reginald,

Yes, you can definitely filter columns by both color and text in Excel. Here are the steps:

Select the column that you want to filter.
Click on the "Filter" button in the "Data" tab.
Click on the drop-down arrow in the column header and select "Filter by Color".
Choose the color that you want to filter out (in this case, gray).
Click on the drop-down arrow in the column header again and select "Text Filters".
Choose "Does Not Equal" and enter "Y" in the text box.
Click "OK" to apply the filter.

This will filter out all the gray cells and the cells with "Y" in the selected column. You can then copy and paste the filtered data to a new sheet or work with it as needed.

Shane Devenshire

Filter but both color AND Text

Hi,

The trick to doing this is to create a second column which refers to the Y,
N and then apply the filter using both columns:

Suppose your Y/N color column is A1:A100 with titles on the first row, in B1
enter "Dummy" and in B2 enter
=A2
Copy this down.
Apply the filter to both columns. From the column A filter pick Color is
Gray. From the column B filter pick Y.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire

"Regalibar" wrote:

Hi All,

Is there a way to filter columns by both color and text? Some rows have
color some rows have text (Y/N). And I need to filter out all the gray cells
and the cells with "Y"

regards

Reginald

Chris Bode

Well, you can achieve this task using macro
Add a command button on the sheet from the control box and paste following codes in codes window

Formula:


		
			
Private Sub CommandButton1_Click()
    Dim row As Integer, col As Integer
    row = 1
    col = 1
    
       
    For row = 1 To 10
        If Sheet1.Cells(row, col).Interior.Color = 12632256 Then
            Exit Sub
        ElseIf Sheet1.Cells(row, col).Value = "Y" Or Sheet1.Cells(row, col).Value = "N" Then
            Exit Sub
        Else
            Sheet1.Rows(row).Delete
        End If
    Next
    
End Sub

Chris
------
Convert your Excel spreadsheet into an online calculator.
http://www.spreadsheetconverter.com

All times are GMT +1. The time now is 01:26 PM.