Hello,
I use Excel 2019.
I have asked this question at stackoverflow.com but no reaction from there.
Question:
Worksheet has +50000 rows and 40 columns with data.
In column B I have coloured cells and cells with No Fill. All cells are numbers.
I want to autofilter with crit(1) to crit(10) as below code show.
crit 1-5 and crit 9-10 works but how to do with "No Fill"? As I understand there is no RGB for "No Fill".
Dim crit(1 To 10) As Long
crit(1) = RGB(255, 0, 255) 'Magenta
crit(2) = RGB(0, 0, 255) 'Blue
crit(3) = RGB(146, 208, 80) 'Green
crit(4) = RGB(255, 192, 0) 'Orange
crit(5) = RGB(0, 255, 255) 'Light Blue
crit(6) = Cells.Interior.ColorIndex = 0 'No fill
crit(7) = Cells.Interior.ColorIndex = 0 'No fill
crit(8) = Cells.Interior.ColorIndex = 0 'No fill
crit(9) = RGB(255, 0, 0) 'Red
crit(10) = RGB(255, 0, 0) 'Red
ws4.Activate
ws4.AutoFilterMode = False
ws4.range("A1:AR1").AutoFilter
LR = ws4.Cells(Rows.count, "A").End(xlUp).row
Set rng = ws4.range("A1:AR" & LR)
For i = 1 To 10
rng.AutoFilter Field:=41, Criteria1:=">= 200000"
rng.AutoFilter Field:=30, Criteria1:="1"
rng.AutoFilter Field:=5, Criteria1:=i
rng.AutoFilter Field:=2, Criteria1:=crit(i), Operator:=xlFilterCellColor
'Do something here
ws4.AutoFilterMode = False
Next i
Any help will be much appreciated.
Thanks in advance!
Bookmarks