Filter Collection Criteria for Colored Excel Rows

[daultani]

Hello,

I am trying to filter my excel with only non colored rows(Basically rows except Red color)  with below Criteria:



Seems like this is not working.
Can anyone suggest the criteria what should I use to get only non colored rows?

Thanks in advance.

------------------------------
Bhawana Daultani
------------------------------

[EmersonF]

Hi @BhawanaDaultani I believe that the criterion will be "RGB" look like this

Criteria1:=RGB(255,0,0)

​

------------------------------
Emerson Ferreira
Cons, Intelligent automation
Avanade Brasil
Recife
+5581988869544
If my answer helped you? Mark as useful!
------------------------------

[daultani]

@EmersonF, Thanks for your reply.
I tried above option too but still no result found. 

I just have to write RGB(255,0,0) in Criteria field Value? or am I missing something?

------------------------------
Bhawana Daultani
------------------------------

[LeonardoHermín1]

Hi @BhawanaDaultani, maybe you has not put the input "Operator", to RGB filter method (enumerator excel xlFilterCellColor) is the number 8.

So:
Criteria:= RGB(255,0,0)
Operator:= 8​​

------------------------------
Leonardo Hermínio
------------------------------

[EmersonF]

Hey @BhawanaDaultani the solution of @LeonardoHermín1 work to me!​​​

------------------------------
Emerson Ferreira
Cons, Intelligent automation
Avanade Brasil
Recife
+5581988869544
If my answer helped you? Mark as useful!
------------------------------

[daultani]

Hi @LeonardoHermín1,

Unfortunately I am getting below error:
"AutoFilter method of Range class failed"



------------------------------
Bhawana Daultani
------------------------------

[herminio217]

Please, can you show the code in the code stage 'Filter'?

------------------------------
Leonardo Hermínio
------------------------------

[daultani]

Input as

@LeonardoHermín1- Let me know in case you need more information.
------------------------------
Bhawana Daultani
------------------------------

[_Amlansahoo]

Hi Bhawana,

Did you get how to pass colour Code criteria Dynamically?

I am also facing the same issue I am not able to send colour code dynamically as of now If I am hardcoding RGB(255,255,0) is working fine.

@EmersonF, please help me if you know how to pass dynamic colour code.

------------------------------
Amlan Sahoo
Senior RPA Consultant
WonderBotz
------------------------------

[devneetmohanty07]

Hi @_Amlansahoo 

You can follow the following approach by extending the MS Excel VBO business object with a separate action built using the below workflow:

The code stage will look something like below:

Dim ws As Object

ws = GetWorksheet(Handle, Workbook, Worksheet)

ws.Activate()

ws.Range("A1").AutoFilter(Field:= Field, Criteria1:= RGB(R,G,B), Operator:=8)

If you want, you can make range dynamic as well. However, my example I am assuming the table begins from the cell "A1". Now, when I use this action, you can see I am able to dynamically pass the RGB values (23,23,23) and it filters accordingly:

------------------------------
----------------------------------
Hope it helps you out and if my solution resolves your query, then please mark it as the 'Best Answer' so that the others members in the community having similar problem statement can track the answer easily in future

Regards,
Devneet Mohanty
Intelligent Process Automation Consultant | Sr. Consultant - Automation Developer,
WonderBotz India Pvt. Ltd.
Blue Prism Community MVP | Blue Prism 7x Certified Professional
Website: https://devneet.github.io/
Email: devneetmohanty07@gmail.com

----------------------------------
------------------------------