Conditional formatting in excel sheet if yes value there in column a then It Should appear in red

Hi All

I am trying to perform Conditional formatting.

I.e. I have an excel from that using a Column(Attribute A) if any yes values found I want to mark that values in Dark red colour.
If the column contains No value then in green colour

Please someone help me to achieve this.

Thanks in advance.

Hello @mettudp075, try this:

Excel Application Scope
Input: Path to Excel File

Read Range
Input: Excel sheet name, Range, Output DataTable: dtData

For Each Row in dtData
Assign
string cellValue = row(“Attribute A”).ToString ’ Replace “Attribute A” with the actual column name

  If cellValue = "Yes" Then
     Excel Set Range Color
        Input: Excel sheet name, Range: "A" + row.Index.ToString, Color: Dark Red
  ElseIf cellValue = "No" Then
     Excel Set Range Color
        Input: Excel sheet name, Range: "A" + row.Index.ToString, Color: Green
  End If

End For Each

Write Range
Input: Excel sheet name, Range: “A1” (or the desired starting cell), DataTable: dtData

Cheers! :slight_smile:

Thank you for the quick response.

Can you suggest me any solution use linq or v script code i had 7000 records i need to avoid using for each row activity.

Thanks

Option 1: Using LINQ (C#)

  1. Read the Excel data into a DataTable using the “Read Range” activity.
  2. Use LINQ to filter and modify the data based on the “Attribute A” column.
  3. Update the Excel file with the modified data using the “Write Range” activity.

var filteredData = dtData.AsEnumerable()
.Select(row =>
{
if (row.Field(“Attribute A”) == “Yes”)
row.SetField(“Attribute A”, “Dark Red”);
else if (row.Field(“Attribute A”) == “No”)
row.SetField(“Attribute A”, “Green”);
return row;
})
.CopyToDataTable();

dtData = filteredData;

Option 2: Using VBA Script (Excel Macro)

  1. Create an Excel VBA macro to perform the conditional formatting based on your criteria.
  2. Save the macro-enabled Excel file.
  3. Use the “Execute Macro” activity in UiPath to run the VBA macro. Provide the path to the Excel file and the macro name.

Sub FormatData()
Dim ws As Worksheet
Dim cell As Range

Set ws = ThisWorkbook.Sheets("YourSheetName") ' Replace with your sheet name

For Each cell In ws.Range("A2:A" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row)
    If cell.Value = "Yes" Then
        cell.Interior.Color = RGB(139, 0, 0) ' Dark Red
    ElseIf cell.Value = "No" Then
        cell.Interior.Color = RGB(0, 128, 0) ' Green
    End If
Next cell

End Sub

Cheers! :slight_smile:

1 Like

Thanks alot.

it’s working as expected with Vba macros.
I had another query in the process if you have any solution please comment out.
I need to perform row comparison from one Data table to another Datatable if both rows matches i need to combine two data rows into single row and save it in output data table with 12 columns in 6 columns from Data Table 1 and 6 Data Table 2 Do you have any solutions for this query.

Note: Data Table 1 has 6 columns
Data Table 2 has 6 columns
Column headers names are Different
(Period , PERIOD_Info)
Most of values same

Thanks in Advance

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