Excel Automation for coloring the text based on the condition

Help Activities
,
1

I have the excel that contains X Column having four rows.
Task should be,
Get the length of the each X row values using length function, if length is greater than six, I want to color the text only without using the format cells activity.
Eg:
X
12345
123456
12345
1234567

I want to color the 1234567 value because it contains more than 6 letters.

Kindly guide me.

2

Hi @Iswarya_P1

Try this VBA scrip, this is looking at column A you can change it to the correct one:

Sub ApplyConditionalFormatting()
    Dim rng As Range
    Dim lastRow As Long
    Dim formulaRange As Range
    
    Set rng = Range("A:A") ' Modify the column as per your requirement
    
    ' Clear any existing conditional formatting
    rng.FormatConditions.Delete
    
    ' Get the last used row in the column
    lastRow = Cells(Rows.Count, rng.Column).End(xlUp).Row
    
    ' Set up the formula range to cover the column from the first row to the last used row
    Set formulaRange = Range(rng.Cells(1), rng.Cells(lastRow))
    
    ' Set up the conditional formatting rule
    With formulaRange.FormatConditions.Add(Type:=xlExpression, Formula1:="=LEN(" & rng.Cells(1).Address & ")>6")
        .Interior.Color = RGB(255, 0, 0) ' Red color
    End With
End Sub
3

Hi @Iswarya_P1 !
To achieve your task of coloring specific values in an Excel column based on their length using VB code in UiPath, you can use the following approach:

  1. Read the Excel file: Use the “Read Range” activity in UiPath to read the Excel file and store the data in a DataTable variable.

  2. Iterate through the rows: Use a For Each Row activity to loop through each row of the DataTable.

  3. Check length and apply color: Inside the loop, use an If activity to check the length of the value in the “X” column. If the length is greater than six, you can apply color using the following VB code:

If row("X").ToString().Length > 6 Then
    ' Select the cell and apply color
    row("X").ToString().Select()
    ExcelApp.Selection.Font.Color = RGB(255, 0, 0) ' Set the color to red
End If

Make sure to replace “X” with the appropriate column name in your Excel file.

4

In this code what does the ExcelApp denotes. Can you explain it? while using this code, It pops up ExcelApp is not declared. then the row is not declared error. Kindly guide me to complete the task.
Thanks in advance.

5

@Iswarya_P1 : Hi Iswarya, Please find attached workflow for your reference, as per your requirement:
ColorExcelCellText.zip (14.3 KB)

Please let me know in case any issues.

Cheers !!!

6

Still the Logic for this coloring the text was not supported. Totally, I have 48 fields. So, I have shared my sample workflow, I want to implement that coloring the font login in else block.

Kindly refer and guide me.

7

Main.xaml (8.5 KB)
New Microsoft Excel Worksheet.xlsx (7.8 KB)
WorkOrder.xaml (6.3 KB)

8

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