Is it possible to set range color for all column headers that start with "abc"?

[jdlee]

I understand that I can specify the range with A:A, but this would be tedious to do for 30+ columns. Is there a way to specify the range for all columns with a header string that starts with ABC?

[prasath17 (Guru)]

@jdlee - Please find the starter help here…

ColumnLetter = Chr(65+Colidx).ToString
Else Case ColumnLetter = “A” +Chr(65+Colidx-26).ToString

Output

Hope this helps…

Please try this…let us know if you need any assistance…

[jdlee]

Thank you for your help. I think I have everything set correctly, but I am getting an error “the range does not exist” when i run it.

[prasath17 (Guru)]

@jdlee - Looks like you are so close…Could you please print the range as I showed in the screenshot above using the below code…

row(EachColumn).ToString + " Colum Idx= " + Colidx.ToString + " Row Idx= " + Rowidx.ToString + " " + ColumnLetter + (Rowidx+1).ToString

Comment the set range color for the time being and check whether you are getting everything correctly.

[jdlee]

Here is the output

05/10/2021 18:01:23 => [Debug] Execution started for project: UiPath_Dev
05/10/2021 18:01:24 => [Info] UiPath_Dev execution started
05/10/2021 18:01:25 => [Info] 1 Colum Idx= 0 Row Idx= 0 1
05/10/2021 18:01:26 => [Error] Set Range Color: The range does not exist.
05/10/2021 18:01:26 => [Info] UiPath_Dev execution ended in: 00:00:01
05/10/2021 18:01:26 => [Error] RemoteException wrapping UiPath.Excel.ExcelException: The range does not exist.  ---> RemoteException wrapping System.Runtime.InteropServices.COMException: Exception from HRESULT: 0x800A03EC 
   at Microsoft.Office.Interop.Excel._Worksheet.get_Range(Object Cell1, Object Cell2)
   at UiPath.Excel.WorkbookApplication.SetRangeColor(String range, Color inputColor)
	--- End of inner exception stack trace ---
   at UiPath.Excel.Activities.ExcelInteropActivity`1.EndExecute(AsyncCodeActivityContext context, IAsyncResult result)
   at System.Activities.AsyncCodeActivity.System.Activities.IAsyncCodeActivity.FinishExecution(AsyncCodeActivityContext context, IAsyncResult result)
   at System.Activities.AsyncCodeActivity.CompleteAsyncCodeActivityData.CompleteAsyncCodeActivityWorkItem.Execute(ActivityExecutor executor, BookmarkManager bookmarkManager)

[Yoichi]

Hi,

FYI, Another solution:

Sample20210510-2.zip (10.7 KB)

Regards,

[prasath17 (Guru)]

@jdlee - Here you go…SetRangeColor_ColumnHeaders.zip (48.7 KB)

You have 2 solutions now…

[jdlee]

Thanks!

[jdlee]

Thanks, this one worked too!