How to perform Conditional Format using Uipath

Hi All,

Good Morning!

I am trying to perform Conditional formatting.

I.e. I have an excel from that using a Column(RRN) if any duplicate values found I want to mark that duplicate values in light red colour.
If the column contains unique value then I don’t want to do anything(it should be white colour as original).

Please someone help me to achieve this.

Thanks in advance.

Book.xlsx (12.8 KB)

@pravin_calvin
@Palaniyappan
@ppr
please help on this

HI @HeartCatcher

Check with the below vba Code!

Sub testmacro()
'declare variables
Dim ws As Worksheet
Dim ColorRng As Range
Dim ColorCell As Range
Set ws = Worksheets("Sheet1")
Set ColorRng = ws.Range("C2:C13")
'highlight cells that contain duplicate values in the selected range
For Each ColorCell In ColorRng
If WorksheetFunction.CountIf(ColorRng, ColorCell.Value) > 1 Then
ColorCell.Interior.Color = RGB(255, 127, 127)
Else
ColorCell.Interior.ColorIndex = xlNone
End If
Next

End Sub

Regards

Thanks for your reply.
I am getting an error

Hi @HeartCatcher

Use Excel application scope inside that use macros!

Enable trust Centre setting in excel application!

Excel options---->TrustCentre—>trustcentreSettings–>macro Settings—.>EnableVBA Macros–.ClickOk

Regards

I did but I am not getting how to use the VBA code, I Saved in a text file trying to execute in Invoke VBA activity but I am not getting the required output.

Please do needful

Hi,

Macro will be fast. Still if you face issues, you can try this workaround
You can follow the below steps:
Step1

Read the Excel using ReadRange

Step2

Add a column Duplicate which will act as a flag later to set the color in the excel

Step3

Use Assign activity and the below query to fetch the duplicate values.
USE A VARIABLE of type System.collections.Generic.IEnumerable to assign the values retrieved using below LINQ:

dtData.AsEnumerable().GroupBy(Function(i) i.Field(Of Double)(“RRN”)).Where(Function(g) g.Count() > 1).Select(Function(g) g.Key)

I have assumed the column datatype as Double,you can modify based on your column datatype.

Step 4

Then loop through the duplicates enumerable and set the value to Duplicate column in datatable dtData to 1 in the datatable using LINQ

Step 5

Filter the datatable dtData and output it to another dtFilter where Column Duplicate=1

Step 6

Inside an Excel App scope ,
loop through the filtered datatable and get the row index of the filtered row from initial datatable
Add 1 to it,as first row in excel will be header
intIndex=dtData.Rows.IndexOf(CurrentRow)+1

Use Set Range color activity to color the cell/row

Hope this helps.

If this gets resolved, please close the thread by marking it as solution. So that it will help others who are facing similar solution.
Thank you.

@geetishree.rao ,

Thanks for your reply.
I tried as you suggested but I am getting an error as in the attachment

Hi ,
I have attached 2 screenshots to resolve the error…
The first variable to retrieve duplicates is System.Collections.Generic.IEnumerable

See the variable type and properties window
Use For each row in datatable to update column to 1
And use Invoke code to update column to 1 for duplicate records

Duplicate.xlsx (18.4 KB)
Thanks for your response.
If you dont mind can you implement the same on the excel.(C column)“RRN”

Sharing the xaml developed using the DuplicateExcel.I have deleted the Duplicate column from excel
Any modifications if u wish,try to explore and implement

You can optimize it yourself.

Do you want live notifications when people reply to your posts? Enable Notifications​

UiPath Community 2021.8 Preview Release

New installer

We now have a new installer for the UiPath Robot/Studio/Assistant.
Your typical Community installation is now available under the Quick Setup option.

What makes it shine though is the fact that you can now install the Robot in Service Mode and therefore fully utilize the Unattended Robot on a free license. For that, follow the steps below:

image007image008image009

Update

With this installer you will get a brand new update mechanism that will help you stay on the latest release. Although we know all of you are waiting for this, we decided to gradually release this content in the next couple of weeks.

Some of you may get multiple updates in the next period of time as we get our new update mechanism released. We really appreciate your patience with this and hope it’s not a big interruption of your RPA flow. We promise that it is for a better auto-update mechanism of the Robot from the Orchestrator.

Known issue: As we are slowly introducing a new method of auto-updating the robots, you will see in Orchestrator under User Sessions your version as being non-compliant. This does not affect functionality in any way and the message will disappear after we release the 21.10 version and you’ll get updated.

Studio

Unified Profile

All the testing capabilities and C# projects are now available in Studio profile. The Studio Pro profile and capabilities have been merged into Studio. All your existing projects can be opened and executed in Studio starting with 21.8. No extra steps or configuration is needed if you previously had Studio or Studio Pro.

Cross-platform Projects

Our .NET 5 migration comes with the ability to execute automations on various operating systems. We are introducing a new compatibility option when you start a new project that will filter cross-platform activities.

Marketplace Templates

We are bringing our Marketplace templates into Studio. Everyone is able to start a project using a template from the dedicated Templates sections in Studio. This brings a lot of exciting ideas for our community.
Learn how to contribute to Marketplace: Introduction for Publishers

Continous Improvements

  • Expression Editor will remember layout and zoom level between sessions;
  • Ability to Copy Repository URL from Project Panel when the current project is stored under source control;
  • Recent section from Start page will display tag information about language, compatibility, and source control.

StudioX

Source Control through GIT

We are pleased to introduce source control management using Git for StudioX. The capability should make source control management easy to use by taking away requirements to understand Git concepts or commands. Instead, you can view a repository just like a folder in Explorer - you can create projects, each within its own folder and save them there.

Once this feature is enabled, you should be able to configure the repositories you have access to:
image001

When you create a new project, you will have the option to save it to your choice of Git repository:
image002

You can also continue work from an existing project using Open from Source Repository:
image003

When closing or publishing your project, you will be able to save your changes to the repository and in the background it will commit them to the source repository:
image004

Preventing Production Runs from StudioX

To avoid production runs of projects from StudioX, we’ve introduced a new governance setting that will monitor automations ran for production purposes. When enabled, you will be notified that you must publish your project to Orchestrator and run your automation from Assistant.

You will be able to configure this setting in the Automation Ops policy by configuring the number of runs allowed as well customizing the user message. For more details on Governance, please check our guide here.

Save Project As

This feature enables you to save a copy of your project as a new project, allowing you to create a completely new Task leveraging existing code.
image005

Additionally, the Save as functionality will allow you to convert a task into a template.

Improvements to existing activities

In order to use these, you’ll need to update your System package to the latest version

Text to Left/Right: Allows you to use special characters (new line, tab, comma etc) as separators within the activities

New Activities

In order to use these, you’ll need to update your System package to the latest version

  • Rename file: This will allow you to rename a file within a task using an activity.
  • Rename folder: This will allow you to rename a folder within a task using an activity.
  • Add data row: Add a row to a data table using a row or list input.
  • Remove data row: Remove a row from a data table using a row or its index from the data table.

Mail activities

For Each Email activity has been enhanced with “Include subfolders” option which allows the user to specify whether to expand the search to include all subfolders of the selected mail folder.

Outlook Desktop in Picture-in-picture (PiP): now a workflow containing Outlook Desktop activities can run in a PiP session even if Outlook application is opened in the Main session.

OneDrive and SharePoint activities

When installing UiPath.MicrosoftOffice365.Activities 1.10.0-preview in StudioX, a new set of activities for OneDrive and SharePoint files and folders is available for citizen developers. These activities should be used within Use OneDrive & SharePoint card activity, where you select the account to use for OneDrive and SharePoint automation.

The list of activities:

  • Get File/Folder
  • Create Folder
  • Copy File/Folder
  • Move File/Folder
  • Delete File/Folder
  • Upload File
  • Download File
  • Export File as PDF
  • Share File/Folder

In Get File/Folder activity you can select a file of folder to use in other activities. We’ve implemented a “Select remote file or folder” browser to pick an item from the cloud locations accessible to the user account specified in Use OneDrive & SharePoint card. There are three possible cloud locations:

  1. OneDrive: My Files and Shared with me
  2. SharePoint: All SharePoint sites followed by the user
  3. Groups: All SharePoint document libraries associated with the Microsoft 365 groups the user belongs to.
    image006

Excel Activities

In order to use these, you’ll need to update your Excel package to the latest version

  • Excel Process Scope: A scope activity that behaves similar to SequenceX, loading the project settings and applying them to the associated Excel process. Additional settings:
    • Process mode: control whether it creates a new process, reuses an existing process if available, or behaves like StudioX for attended users.
    • File Conflict Resolution: determines the behavior in a way that supports both unattended and attended automation
    • Existing Excel Processes: Offer capability to manage existing processes on the machine (Same as project, Force kill, No action)

Assistant

Processes available in Start Menu

All available processes from Assistant can be added as shortcuts inside the Start Menu. In the General Settings page, you can find a toggle for “Show in Windows Start Menu”. The default value is On and when the toggle is Off, the processes will be removed from the Start Menu.

image010

Processes grouped by folders

With this new feature you will be able to change the way of showing the processes list in the Assistant home tab. In the General Settings page, you can find a toggle called “Group by folder”. By default, the folder hierarchy is Off and when the toggle is On, the processes list will be grouped by folders. In both cases, the sections will be collapsible.

image011

UIAutomation

Nested Use-app cards

Until today, automating multiple apps simultaneously can sometimes be cumbersome, as it requires careful nesting of Use application/browser cards, and placing UI activities in the correct card. Starting today, no more! You can just nest Use application/browser cards however you want, and inside them, configure each UI activity to reference whichever card you need.

image013

Excel cards as App cards

Starting with 21.8, StudioX’s Use Excel File is promoted to act as an Use app card. Meaning, now you can add a single Use Excel card and inside it, place both specific Excel activities, as well as any UI automation activities, like Click, Type, and so on, and they will work just like inside an Use app/browser . This will greatly increase the flexibility of automating Excel with a mix of dedicated Excel activities + UI automation activities, to achieve any and all Excel functionality.

image014

Get Attribute

This Classic activity has been revamped and upgraded to fit in the Modern package. It serves the same purpose of extracting the value of certain attributes, but it’s been enhanced with better explanations about what each attribute is for, and a live view of the current values of the attributes.
image015

Mouse Scroll

A highly requested feature, this scroll activity is smart! It allows the basic row-based scrolling, as well as To-element scrolling, for those situations when you need to scroll to a specific object on the page. On top of that, it supports “endless” pages, as a lot of modern websites are built this way.

image016

Edit Table Extraction

The table extraction wizard can now be restarted to resume work from where it was left. The already added data is highlighted so you can remove it, add new data or configure the columns properties.

image017

Type Into - Automatically set the correct input method

When building UI automations when building UI automations, setting the correct input method is paramount, as it can greatly increase reliability and speed. Unfortunately, picking the correct one is a bit of a black art, as there is no hard rule to follow, so some trial & error is involved. This new improvement automates this process, performing all the trial & error for you, with the click of a button, located right next to the input mode. Enjoy!

SAP Automation

SAP WinGUI automation improvements

  • Click with hardware events will perform scrolling to the right table cell independent on the cell position (“in view” / “out of view”)
  • Click with hardware events do not scroll the table anymore if the cell is “in view” to reduce the execution time
  • Click with hardware events works now also on SAP ALV Tables
  • Default selector for column headers in SAP Tables is using ColTooltip instead of tableCol to provide consistency across the whole table identification approach.

SAP FIORI automation improvements

  • Backwards compatibility improvement: Auto-scrolling for tables is now supported even for SAP Fiori UI versions older than 1.78. All available browsers such as IE, Firefox, Chrome and Edge are supported.
  • SAP FIORI UI5 Attributes are now fully visible and available in Modern UI Activities.

Activities

Mail activities

Mail package brings a new activity Outlook Mail Messages Trigger which allows you to set up a trigger to watch for incoming/outgoing mail messages that match a filter criteria.

image019

Get IMAP Mail Messages can work with mail providers like 163.com. When the new parameters ClientName and ClientVersion are set, the activity identifies the client implementation to the server.

There are some bug fixes as well:

  • Fixed an issue in Save Attachments that caused the activity to fail with “length cannot be less than 0” for some emails.
  • An issue has been fixed in Save Mail Message activity. Now the activity creates the missing folder structure.
  • Fixed an issue in Exchange activities in case of O365 Interactive authentication that prompted the user to provide the credentials at each run.
  • An issue has been fixed for Send Exchange Mail Message when SaveCopy option was checked that caused an email with attachments to not appear in the shared mailbox’s Sent Items folder.

IT Automation

UiPath.AmazonWebServices.Activities 2.0.0 brings a new set of activities to help automate AWS IAM (Identity & Access Management) operations. Examples of operations that can be automated using these new activities:

  • Users: create/update/delete users, change user password.
  • Groups: create/update/delete groups, add/remove users to/from group.
  • Roles: create/update/delete roles, add/remove roles to/from instance profiles.
  • Policies: create/update/delete inline and managed policies, add/remove policies to/from IAM identities (users, groups, roles).

For Each Object in Bucket and Download Object to File activities have been updated with new parameters to make it easier to deal with folders within a bucket.

image020

Document Understanding

It is finally out! We have worked on redesigning the Taxonomy Manager, migrating it from Angular 1 to Angular 11! Experience a new design that allows you to manage document types and fields, organized by categories and groups - and on top, define custom colors & hotkeys for fields, so that working with them in the Validation and Classification Station is easier than ever!

image021

Besides these, we have also worked on enhancements of the digitization algorithm, as well as fixed some bugs here and there.

Orchestrator

Starting with this release, you will see the new client apps (Robot, Studio, Assistant) Auto-update feature in Orchestrator. You can read more about it and about couple of other cool features we’ve released here.

Task Capture

Import MS Visio Diagram

Do you already have a created process map using MS Visio and want to continue documenting the process in Task Capture? Easily open MS Visio files in Task Capture instead of creating the diagram from scratch!

Leverage the updated recorder

We’ve changed the recorder component to improve the quality of selectors Task Capture generates for Studio output. It’s already set up as a default recorder, so just capture your process, export it to Studio in XAML format and check out results!

Want to find out more? Please visit the separate post dedicated to Task Capture 21.8 updates here:

Please provide feedback

We want to hear which feature excited you the most! Please use the image button below.

Jump to the comments :heart:

How to perform Conditional Format using Uipath

HelpActivities

questionactivitiesexcel

8

/

11

HeartCatcherNaveenkumar.S

7h

Hi All,

Good Morning!

I am trying to perform Conditional formatting.

I.e. I have an excel from that using a Column(RRN) if any duplicate values found I want to mark that duplicate values in light red colour.
If the column contains unique value then I don’t want to do anything(it should be white colour as original).

Please someone help me to achieve this.

Thanks in advance.

Reply

  • created

7h

  • [

last reply

](How to perform Conditional Format using Uipath - #11 by HeartCatcher)

3h

  • 10

replies

  • 32

views

  • 3

users

  • 7

HeartCatcherNaveenkumar.S

7h

Book.xlsx (12.8 KB)

1 Reply

Reply

HeartCatcherNaveenkumar.S

HeartCatcher

7h

@pravin_calvin
@Palaniyappan
@ppr
please help on this

1 Reply

Reply

pravin_calvinpravin kumarRobot Master

1

HeartCatcher

7h

HI @HeartCatcher

Check with the below vba Code!

Sub testmacro()
'declare variables
Dim ws As Worksheet
Dim ColorRng As Range
Dim ColorCell As Range
Set ws = Worksheets("Sheet1")
Set ColorRng = ws.Range("C2:C13")
'highlight cells that contain duplicate values in the selected range
For Each ColorCell In ColorRng
If WorksheetFunction.CountIf(ColorRng, ColorCell.Value) > 1 Then
ColorCell.Interior.Color = RGB(255, 127, 127)
Else
ColorCell.Interior.ColorIndex = xlNone
End If
Next

End Sub

Regards

1 Reply

Reply

HeartCatcherNaveenkumar.S

6h

Thanks for your reply.
I am getting an error

1 Reply

Reply

pravin_calvinpravin kumarRobot Master

1

HeartCatcher

6h

Hi @HeartCatcher

Use Excel application scope inside that use macros!

Enable trust Centre setting in excel application!

Excel options---->TrustCentre—>trustcentreSettings–>macro Settings—.>EnableVBA Macros–.ClickOk

Regards

1 Reply

Reply

HeartCatcherNaveenkumar.S

pravin_calvin

6h

I did but I am not getting how to use the VBA code, I Saved in a text file trying to execute in Invoke VBA activity but I am not getting the required output.

Please do needful

Reply

geetishree.raoGeetishree RaoUiPath

1

6h

Hi,

Macro will be fast. Still if you face issues, you can try this workaround

You can follow the below steps:
Step1

Read the Excel using ReadRange

Step2

Add a column Duplicate which will act as a flag later to set the color in the excel

Step3

Use Assign activity and the below query to fetch the duplicate values.
USE A VARIABLE of type System.collections.Generic.IEnumerable to assign the values retrieved using below LINQ:

dtData.AsEnumerable().GroupBy(Function(i) i.Field(Of Double)(“RRN”)).Where(Function(g) g.Count() > 1).Select(Function(g) g.Key)

I have assumed the column datatype as Double,you can modify based on your column datatype.

Step 4

Then loop through the duplicates enumerable and set the value to Duplicate column in datatable dtData to 1 in the datatable using LINQ

Step 5

Filter the datatable dtData and output it to another dtFilter where Column Duplicate=1

Step 6

Inside an Excel App scope ,
loop through the filtered datatable and get the row index of the filtered row from initial datatable
Add 1 to it,as first row in excel will be header
intIndex=dtData.Rows.IndexOf(CurrentRow)+1

Use Set Range color activity to color the cell/row

Hope this helps.

If this gets resolved, please close the thread by marking it as solution. So that it will help others who are facing similar solution.
Thank you.
DuplicateValues.xaml (17.7 KB)

@geetishree.rao,

I didn’t find any .xaml file can you please share again.

DuplicateValues.xaml (17.7 KB)

Thank you for help the method which you sent is working fine but its highlighting the entire rows(Which contains duplicate values) but I need to highlight only "RRN column which contains duplicate values can you please help me to do so.

Sorry for the late reply.

Thanks.

Great … it helped you.
Please could you close this and mark it as the solution to your query so that it helps others…

Just make the below change, if you want to change the colour of RRN column which is column C as per your excel

Set Range Color Activity:

Range as
“C”+intExcelIndex.ToString+“:C”+intExcelIndex.ToString
Or Just
“C”+intExcelIndex.ToString

Thanks

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.