cancel
Showing results for 
Search instead for 
Did you mean: 

Filter Collection Criteria for Colored Excel Rows

daultani
Level 3
Hello,

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

24135.png

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
------------------------------
10 REPLIES 10

EmersonF
MVP
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!
------------------------------
Sr Cons at Avanade Brazil

@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
------------------------------

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
------------------------------

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!
------------------------------
Sr Cons at Avanade Brazil

Hi @LeonardoHermín1,

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

24102.png

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

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

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

24106.png


Input as

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

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
------------------------------
Regards,
Amlan Sahoo

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:

24115.png

The code stage will look something like below:

24116.png

Dim ws As Object

ws = GetWorksheet(Handle, Workbook, Worksheet)

ws.Activate()

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

24117.png

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:

24118.png



------------------------------
----------------------------------
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

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

---------------------------------------------------------------------------------------------------------------------------------------
Hope this helps you out and if so, please mark the current thread as the 'Answer', so others can refer to the same for reference in future.
Regards,
Devneet Mohanty,
SS&C Blueprism Community MVP 2024,
Automation Architect,
Wonderbotz India Pvt. Ltd.