topic RE: Excel Font Color in Product Forum

Excel Font Color

aanchalm — Fri, 07 Apr 2023 14:17:00 GMT

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

RE: Excel Font Color

Mukeshh_k — Fri, 07 Apr 2023 16:44:00 GMT

Hi @aanchalm : 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
------------------------------

RE: Excel Font Color

aanchalm — Fri, 07 Apr 2023 17:14:00 GMT

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

RE: Excel Font Color

Mukeshh_k — Fri, 07 Apr 2023 17:30:00 GMT

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

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 -
Macro VBA Code:

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

RE: Excel Font Color

asilarow — Fri, 07 Apr 2023 22:04:00 GMT

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

RE: Excel Font Color

aanchalm — Sat, 08 Apr 2023 03:50:00 GMT

Hi ,

It is like snip 2.

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

RE: Excel Font Color

Mukeshh_k — Sat, 08 Apr 2023 12:21:00 GMT

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

2) Add a Code Stage : Set Inputs as below

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)

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:
5) Publish the Action and Call from Process :

7) Result/Ouput:
Excel before Code:

Excel After Code:

Let me know if you face any difficulties implementing this.

RE: Excel Font Color

Mukeshh_k — Sat, 08 Apr 2023 12:33:00 GMT

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

RE: Excel Font Color

aanchalm — Sat, 08 Apr 2023 14:32:00 GMT

Thank you so much for your help.

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