Filter Rows Based on Color

Help Studio
, ,
1

Hi Guys,

I want to filter the excel sheet rows based on the color. Color will be Light Green. Please check the below screenshot.

(Note: Excel where I am doing filtering it has more data so please suggest the solution accordingly.)

Can you please help me with the approach?

image
image625Ă—572 18.4 KB

Follow my blog for the detailed steps with the solution:

3

If you can add an extra column to the file, even at runtime, then you can try this approach:

  • Use For Each Excel Row to loop through the rows of your file
  • (For each row) Use “Get Cell Color” for the Cell in the column you want to filter.
  • Check if the retrieved color matches the light green. If so, update the new column with a fixed value (i.e., TRUE, or “1”, or whatever you want)
  • When you’re done with the filter, use the “Filter” activity on the new column you’ve added (which will only contain values for the rows where the cells have the desired color
4

Hi @lrtetala,

I am not able to see this activity in my studio.

I think you are referring to StudioX.

Thanks!

5

Hi @Irene,

Thanks for your help.

As I said earlier the data is huge and I will not be able to add the extra data column to an excel sheet.

Can you please suggest different approach?

Thanks!

6

@supermanPunch @Gokul001 @ushu @kirankumar.mahanthi1 @Palaniyappan @Sudharsan_Ka @Anil_G

7

Have you tried with Macros @shreyash_shirbhate ?

8

@shreyash_shirbhate

You can try saving a vba as text file and use invoke vba activity


Sub Macro1(ColumnIndex As Integer)


    ActiveSheet.UsedRange.AutoFilter Field:=ColumnIndex, Criteria1:=RGB(255, 255, 0), Operator:=xlFilterCellColor

End Sub

change r g b values as per the color you need…ColumnIndex is the Count of the column

cheers

1 Like
9

@shreyash_shirbhate

Here is the code generated in chat gpt

Option Explicit
Sub FilterColumnByColor(SheetName As String,SheetRange As String)
    Dim ws As Worksheet
    Dim rng As Range
    Dim filterRange As Range
    Dim cell As Range
    Dim filterColor As Long
    
    ' Set the worksheet
    Set ws = ThisWorkbook.Sheets(SheetName) ' Replace "Sheet1" with your sheet name
    
    ' Set the range of the column you want to filter
    Set rng = ws.Range(SheetRange) ' Replace "A1:A10" with your column range
    
    ' Set the color to filter (Light Green RGB value)
    filterColor = RGB(144, 238, 144)
    
    ' Clear previous filters (if any)
    ws.AutoFilterMode = False
    
    ' Filter the column based on color
    rng.AutoFilter Field:=1, Criteria1:=xlFilterCellColor, Operator:=xlFilterFontColor, RGB(filterColor)
    
    ' Get the filtered range
    On Error Resume Next
    Set filterRange = rng.SpecialCells(xlCellTypeVisible)
    On Error GoTo 0
    
End Sub

You can modify some things here and then save in the notepad as “FilterColumnByColor.vb”

  • Use a Excel Application Scope give the file path
    • Invoke VBA and pass the file path and give the Method name as “FilterColumnByColor”
      Give the input parameter {“Sheet2”,“A1:N10”}

Hope this Helps!

Regards
Sudharsan

10

It is available in UiPath.Excel.Activities
It is in Studio

image
image365Ă—821 20.7 KB

11

I don’t see it. Which package version are you using?

image
image434Ă—579 14.2 KB

12

The “Format Cell” activity will change the color of the cell in Excel but it will not provide the result you’re looking for, because it doesn’t filter anything.

1 Like
13

image

14

Hi @Anil_G ,

I need to replace this with my sheet name right? And how to pass column index? My column index is 17. Please see below image and let me know if it is right?

image

I have modified your code in below way please check.


Sub Macro1(ColumnIndex As Integer)
    Data.UsedRange.AutoFilter Field:=ColumnIndex, Criteria1:=RGB(144, 238, 144), Operator:=xlFilterCellColor

End Sub
15

hi @Sudharsan_Ka ,

Can we do this with excluding the sheet range thing?

’ Set the range of the column you want to filter
Set rng = ws.Range(SheetRange) ’ Replace “A1:A10” with your column range

16

Its giving syntax error @Sudharsan_Ka

image
image1491Ă—792 73.5 KB

17

@shreyash_shirbhate

Activesheet will work on the current sheet that is active in excel

And yes the column index is correct but here index is not from 0 it is from 1

Cheers

1 Like
18

O okay, Thanks. Let me try.

SO I don’t need to replace active sheet with my sheet name right?

19

Hi @Anil_G,

I have tried it and it is working but I am not getting the expected result.

I am giving this Criteria1:=RGB(144, 238, 144) color code for light green.

But when I tried with get color code for particular cell then it giving me weird output instead of light green. Which color do you think is this?

image

image
image1797Ă—739 64.5 KB

20

@shreyash_shirbhate

You can do color.R to get R and all …please check the color properties

Cheers

21

Hi @Anil_G,

I did but I am getting wrong value for R10 cell. — 255255255

But for D column it is giving me correct-- 255255183

D column picture.
image

R column picture.
image

what do you think what should be the issue?