Question VBA - Filter by non fill cells background (only colored cells)

[Raiyen]

Hi everyone,

I want to filter by non fill cells background (only colored cells) in VBA Code
Can someone share this tricks

Thanks

 

[Peter_SSs]

Welcome to the MrExcel board!

I want to filter by non fill cells background (only colored cells)

That is not entirely clear to me. If it is that you want to use AutoFilter to hide any cell that are coloured and just show the non-coloured cells, then starting with this sample

22 02 24.xlsm
	A
1	Header
2	a
3	b
4	c
5	d
6	e
Sheet2 (2)

and applying this code

Sub FilterNoColour()
  Range("A1:A7").AutoFilter Field:=1, Operator:=xlFilterNoFill
End Sub

produces this

22 02 24.xlsm
	A
1	Header
2	a
4	c
6	e
Sheet2 (2)

If that is not what you want then perhaps you could explain again and give us your own 'before' and 'after' with XL2BB?

 

[Raiyen]

Thanks Mr. peter for reply , but that's not what I want
in your screenshot I want to filter colored cells only (the green and blue color for example in the screenshot)

meaning the criteria is not equal to non fill color

 

[Peter_SSs]

Then I think that you will need a helper column. Is that acceptable?

 

[Raiyen]

yes sir it is fine

 

[Peter_SSs]

See if you could use something like this then.
Here I want to filter the range A1:C6 by cells in column B being coloured.

22 02 24.xlsm
	A	B	C	D
1	Hdr1	Hdr2	Hdr3
2	Data 1	a	abc
3	Data 2	b	def
4	Data 3	c
5	Data 4	d
6	Data 5	e	mno
7
Filter Colour

Sub FilterColour()
  Dim c As Range
  Dim b As Variant
  Dim i As Long
  
  With Range("A1:D" & Range("A" & Rows.Count).End(xlUp).Row)
    ReDim b(1 To .Rows.Count, 1 To 1)
    b(1, 1) = "Has Colour"
    For i = 2 To .Rows.Count
      If .Cells(i, 2).DisplayFormat.Interior.Color <> 16777215 Then b(i, 1) = "Y"
    Next i
    .Columns(.Columns.Count).Value = b
    .AutoFilter Field:=.Columns.Count, Criteria1:="Y"
  End With
End Sub

22 02 24.xlsm
	A	B	C	D
1	Hdr1	Hdr2	Hdr3	Has Colour
3	Data 2	b	def	Y
5	Data 4	d		Y
7
Filter Colour

 

[Raiyen]

Thanks a lot Mr. Peter, you have my deepest gratitude

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.