Using Format Cells activity in Modern excel

Help Studio
, ,
1

Hello,
i’m trying to iterate through a spreadsheet using excel process scope. i want to look at a particular column using the if statement:
CurrentRow.ByField(“SLA Breached”).ToString.Trim = “Yes”

if it equals “Yes”, i want to change the font to red. Is there a simple logic to add to the source field to update the particular cell? i’m having issues determining what to write.

image
image475×176 4.58 KB

2

Hi @chris.dease

Can you try the below

This image shows a sequence of steps in an automated process within an Excel workbook, including reading rows from "Sheet1," checking if the "Place" field equals "Bang," and formatting specific cells accordingly. (Captioned by AI)
This image shows a sequence of steps in an automated process within an Excel workbook, including reading rows from "Sheet1," checking if the "Place" field equals "Bang," and formatting specific cells accordingly. (Captioned by AI)1147×1264 60.1 KB

Output:

The image shows an Excel spreadsheet with a table containing two columns labeled "Place" and "col1," where "Bang" entries in the "Place" column are highlighted in red and the corresponding numerical data is in the "col1" column. (Captioned by AI)
The image shows an Excel spreadsheet with a table containing two columns labeled "Place" and "col1," where "Bang" entries in the "Place" column are highlighted in red and the corresponding numerical data is in the "col1" column. (Captioned by AI)560×477 6.51 KB

Regards,

3

Don’t use ByField here. Just use CurrentRow(“SLA Breached”).ToString.Trim

You use ByField to get the range:

CurrentRow.ByField(“SLA Breached”).Address

Put that into the Source/Range of your Format Cells activity.

4

You don’t know if their column is A. Shouldn’t hard-code it. You can extract the cell address with…

CurrentRow.ByField(“Column Name”).Address

5

@postwick,
i tried @lrtetala way and it worked when i hard coded the column “D” (in my spreadsheet). i tried your way as well, but i’m getting error: BC30512 disallows implicit conversions from String to IReadRangeRef. Any suggestions?

7

Oh, sorry, I missed part of the expression. It should be:

Excel.Sheet(“your sheet name”).Range(CurrentRow.ByField(“SLA Breached”).Address)

8

This is working! Thanks for you help!!

1 Like
9

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