cancel
Showing results for 
Search instead for 
Did you mean: 

Excel Font Color

AS
Level 4

Hi team,

I have an excel in which a column has red colored text in it/font color is red, I have to filter that column on that basis ,Can anyone tell me how to do that?



------------------------------
aanchal mathur
System engineer
Infosys ltd
------------------------------
1 BEST ANSWER

Helpful Answers

@AS - Understood thanks for clarifying your query : Please follow below approach: 

1) Add a custom action in one of your excel extended VBOs - Add Criteria to Filter by Font Colour - See below and Add inputs as : Handle, Worksheet Name, WorkBook Name, and Field.
25297.png
2) Add a Code Stage : Set Inputs as below
25298.png
3) Write below Code : Note for Criteria its hardcoded to (255, 0, 0) as per your requirement (in Red) - you can remove that colour code and set a user input for custom RGB value if your wish to choose different colour to filter. 

Dim ws As Object
 
ws = GetWorksheet(Handle, Workbook, Worksheet)
 
ws.Activate()
ws.Range("A:Z").AutoFilter(Field:= Field, Criteria1:= RGB(255, 0, 0), Operator:= xlFilterFontColor)


25299.png
4) Add Dll and Namespace: I will attach the dll with this response (Microsoft.Office.Interop.Excel.dll : Free .DLL download)- add this dll to C:\Program Files\Blue Prism Limited\Blue Prism Automate\ :

Dll: Microsoft.Office.Interop.Excel.dll 
NameSpace : Microsoft.Office.Interop.Excel.XLAutoFilterOperator

Additionally you would have to specifiy the DataTable as this belongs to both of these Dll : 'DataTable' is an ambiguous reference between 'System.Data.DataTable' and 'Microsoft.Office.Interop.Excel to let the code know which data table you are using - add reference namespace as : System.Data.DataTable so that system does not get confused which reference to use as you might have other actions which are referenced to this DataTable reference from System.Data

See below: 25300.png
5) Publish the Action and Call from Process :
25301.png
7) Result/Ouput:
Excel before Code:
25302.png
Excel After Code:
25303.png

Let me know if you face any difficulties implementing this.



------------------------------
Kindly up vote this as "Best Answer" if it adds value or resolves your query in anyway possible, happy to help.

Regards,

Mukesh Kumar - Senior Automation Developer

NHS England, United Kingdom, GB
------------------------------

Regards,

Mukesh Kumar

View answer in original post

8 REPLIES 8

Mukeshh_k
MVP

Hi @AS : Is one of the columns name in red font colour or the rows data in the column have red font ? Do you have a sample snip of how it looks like, it will be easy to understand it then?



------------------------------
Kindly up vote this as "Best Answer" if it adds value or resolves your query in anyway possible, happy to help.

Regards,

Mukesh Kumar - Senior Automation Developer

NHS England, United Kingdom, GB
------------------------------
Regards,

Mukesh Kumar

It is a column in which the numbers are entered like 254.78 , these numbers are written in red color, I don't have the snip as much. 



------------------------------
aanchal
------------------------------

Hi aanchal mathur - (Your Query : column has red coloured text in it/font colour is red, I have to filter that column on that basis)- is it the rows in that specific column which are red in colour and you want to filter those rows which has red font- See below is it like this snip?

Snip 
25291.png

 
To try creating a code for above such cases : just hit record the macro and do the excel operations when done stop the macro and try understanding the code:
for your scenario - 25292.png
Macro VBA Code: 
25293.png

You can replicate this code in one of your excel extended objects, additionally you might have to add the Dll : Microsoft.Office.Interop.Excel.dll and Namespace : Microsoft.Office.Interop.Excel.XLAutoFilterOperator


------------------------------
Kindly up vote this as "Best Answer" if it adds value or resolves your query in anyway possible, happy to help.

Regards,

Mukesh Kumar - Senior Automation Developer

NHS, England, United Kingdom, GB
------------------------------

Regards,

Mukesh Kumar

asilarow
MVP

Hi Aanchal,

You will need a new action in the Excel VBO for this..
The below code is in C# but you can easily convert into VB.net:

xs1.Range["A1", xs1.Cells[lastrow.Row, lastcol.Column]].
AutoFilter(14, xlBook.Colors[33], Excel.XlAutoFilterOperator.xlFilterCellColor);

Bear in mind that in this example the colour id is hardcoded as 33.
You will need to find the appropriate color id for your filter:
https://www.automateexcel.com/excel-formatting/color-reference-for-color-index/

------------------------------
Andrzej Silarow
Principal Consultant
RPA Guru
Europe/London
------------------------------

Andrzej Silarow

Hi , 

It is like snip 2.



------------------------------
aanchal mathur
System engineer
Infosys ltd
------------------------------

@AS - Understood thanks for clarifying your query : Please follow below approach: 

1) Add a custom action in one of your excel extended VBOs - Add Criteria to Filter by Font Colour - See below and Add inputs as : Handle, Worksheet Name, WorkBook Name, and Field.
25297.png
2) Add a Code Stage : Set Inputs as below
25298.png
3) Write below Code : Note for Criteria its hardcoded to (255, 0, 0) as per your requirement (in Red) - you can remove that colour code and set a user input for custom RGB value if your wish to choose different colour to filter. 

Dim ws As Object
 
ws = GetWorksheet(Handle, Workbook, Worksheet)
 
ws.Activate()
ws.Range("A:Z").AutoFilter(Field:= Field, Criteria1:= RGB(255, 0, 0), Operator:= xlFilterFontColor)


25299.png
4) Add Dll and Namespace: I will attach the dll with this response (Microsoft.Office.Interop.Excel.dll : Free .DLL download)- add this dll to C:\Program Files\Blue Prism Limited\Blue Prism Automate\ :

Dll: Microsoft.Office.Interop.Excel.dll 
NameSpace : Microsoft.Office.Interop.Excel.XLAutoFilterOperator

Additionally you would have to specifiy the DataTable as this belongs to both of these Dll : 'DataTable' is an ambiguous reference between 'System.Data.DataTable' and 'Microsoft.Office.Interop.Excel to let the code know which data table you are using - add reference namespace as : System.Data.DataTable so that system does not get confused which reference to use as you might have other actions which are referenced to this DataTable reference from System.Data

See below: 25300.png
5) Publish the Action and Call from Process :
25301.png
7) Result/Ouput:
Excel before Code:
25302.png
Excel After Code:
25303.png

Let me know if you face any difficulties implementing this.



------------------------------
Kindly up vote this as "Best Answer" if it adds value or resolves your query in anyway possible, happy to help.

Regards,

Mukesh Kumar - Senior Automation Developer

NHS England, United Kingdom, GB
------------------------------

Regards,

Mukesh Kumar

Attached is the Dll Copy, Additionally you can download this dll from : https://www.dll-files.com/microsoft.office.interop.excel.dll.html 



------------------------------
Kindly up vote this as "Best Answer" if it adds value or resolves your query in anyway possible, happy to help.

Regards,

Mukesh Kumar - Senior Automation Developer

NHS England, United Kingdom, GB
------------------------------
Regards,

Mukesh Kumar

Thank you so much for your help.



------------------------------
aanchal mathur
System engineer
Infosys ltd
------------------------------