Color the empty cells in excel

[Iswarya_P1 (Iswarya P)]

I have 100 rows and 48 columns in the excel in that have lot of empty cells. I want to color the empty cells in the excel.
if have any solutions, Kindly guide me.

thanks in advance.

[Anil_G (Anil Gorthi)]

@Iswarya_P1

Follow the steps

1. Use for each row in excel activity
2. Use for each activity with In argument as Enumerable.Range(0,48).ToArray
3. Then inside the loop use if condition with currentrow.ByIndex(currentitem).ToString.Trim.Equals(String.Empty)
4. On then side use set range color activity or format cels activity and use the color and give range as Excel.Sheet("sheetname").Range(currentrow.ByIndex(currentitem).Address)

Cheers

[Iswarya_P1 (Iswarya P)]

Hi Anil,

As we tried the above logics, Got an error in ByIndex function. Attached the workflow sequence for your reference. Kindly assist me to move further.

Thanks in advance.
BlankProcess2 (2).zip (9.3 KB)

[Yoichi]

Hi,

I just modified your sample. Can you try the following?

Sequence1.xaml (14.3 KB)

Regards,

[Anil_G (Anil Gorthi)]

@Iswarya_P1

What I gave you are the details using excel for each row activity…But I see you read the data and use for each row in datatable…so I modified it as per your logic only…Mode few changes as per the activities you used

BlankProcess2.zip (9.7 KB)

output:

Indexval is the variable assigned to the index property in for each row in datatable…added 2 because 1 for index starting from zero and other for the header. Also have added dt.Columns.Count instead of 48 to make it dynamic for any number of columns

My method is depicted by @Yoichi above , you can try that as well…not much of difference…main is in your’s read the data into datatable in the formers which I provided it uses data directly from excel

cheers