Compare two sheets and change color in both or any one of sheets

Compare two sheets and change color in both or any one of sheets. In those two sheets I want to compare column 2 and Row 2

Hi @Bhushan_Nagaonkar

Read Range (Sheet1) → DataTable1
Read Range (Sheet2) → DataTable2

For Each Row (DataTable1)
Assign: valueFromSheet1 = row(“Column2”).ToString()
Assign: valueFromSheet2 = DataTable2.Rows(0)(DataTable2.Columns.IndexOf(“Column” + (DataTable2.Columns.IndexOf(“Column2”) + 1).ToString())).ToString()

If valueFromSheet1 = valueFromSheet2 Then
    Set Range Color (Sheet1) - Set cell color in Sheet1 for the current row
    Set Range Color (Sheet2) - Set cell color in Sheet2 for the corresponding cell in Row 2
Else
    Set Range Color (Sheet1) - Set a different cell color in Sheet1 for the current row (optional)
    Set Range Color (Sheet2) - Set a different cell color in Sheet2 for the corresponding cell in Row 2 (optional)
End If

End For

Write Range (Sheet1) → Save the modified data back to Sheet1 (optional)
Write Range (Sheet2) → Save the modified data back to Sheet2 (optional)

Hope it helps!!

Hi @Bhushan_Nagaonkar - May I know what values you want to compare

FYI - to set the color in an excel use below activity

https://docs.uipath.com/activities/docs/excel-set-range-color

Yes,

image

This is the format and I want that change color to red or green depending the values

Thanks, I will let you know.

This for dtsheet2

image

@mkankatala Sorry I mightve have stated the question in a wrong way.
I want to compare Column “Places” from Both sheets. The value can be more than that.
If the values of column “Places” match it will show green or Red

Okay @Bhushan_Nagaonkar

=> Use Read Range workbook of Sheet1 → Output → DataTable1
=> Use Read Range Workbook of Sheet2 → Output → DataTable2

=> Use For Each Row in Datatable to iterate the DataTable1
=> Inside for each place assign activies.
→ Assign: placeValue = row(“Places”).ToString()
→ Assign: matchingRow = DataTable2.AsEnumerable().Where(Function(x) x(“Places”).ToString() = placeValue).FirstOrDefault()

=> After two assigns Place an If condition
If matchingRow IsNot Nothing Then
Set Range Color (Sheet1) - Set cell color in Sheet1 for the current row to green
Else
Set Range Color (Sheet1) - Set cell color in Sheet1 for the current row to red
End If
End For each

=> After for each Use Write Range workbook to write the data to Sheet1 → Save the modified data back to Sheet1.

Hope you understand!!

Yes, I did I will let you know.

Is the type wrong

@Bhushan_Nagaonkar Place value variable datatype should be in string.

It is in string somehow Im getting this issue

@Bhushan_Nagaonkar - please check the attached workflow

SampleWorkflow.zip (11.7 KB)

Output

Sheet1 data

Picture1

Sheet2 data

Picture2

Ok sure. Thankyou I will check and let you know.

Hi,

Im getting this error Set Range Color: Failed setting the current sheet to: Sheet1 in workbook: \10.2.3.101\Automation\Bhushan\BlankProcess3\New Microsoft Excel Worksheet.xlsx. Original error message: 0x800401A8
and going into infinite loop
Your flow that you shared is working but when I used the same flow it threw this error.

Should I add delay

@Bhushan_Nagaonkar

Are you trying to rename the current sheet to Sheet1. According to your requirement it should not happen, correct?? The color should set in the existing sheet which is either Sheet1 or Sheet2

Can you please check the right file path that you are giving

It should loop only the rows with data. Please check there were no hidden rows present in your excel

yes, please try adding delay and see how that goes. If possible plz share your workflow as well

Ok I will share mine
MicrosoftTeams-image (127)


MicrosoftTeams-image (129)
MicrosoftTeams-image (130)

It is editing only one cell with red color it shoulve been green

@Bhushan_Nagaonkar Make sure you set the color value to green

Picture1


yes I have