I have an Excel file with a data table inside and I want to color cells that contain a certain string.
I am thinking to use Set Range Color activity for this, but I am not sure how to do this. Can you even use if statement against Excel files, without retrieving the data using Read Range?
I want to iterate each row under a certain column (description) and if the description contains “Error”, I want to color the cell to red.
Either way, you need to calculate the range of the cells in question.
Let’s use the Set Range Color method:
pseudocode:
For each row in dt1 // set Output index in properties = cellIndex
If row("description").ToString.ToUpper.Contains("ERROR")
Then
Assign cellRange = Convert.ToChar(dt1.Columns.IndexOf("description")+65).ToString+(cellIndex+2).ToString
Set Range Color using cellRange. For color, type Color., and after the period a list of colors should show up
so, you’ll need to verify that the Range was calculated correctly first, and if not, make the adjustment.
If the column goes passed column ‘Z’ in Excel, then calculating the letter is more complicated than simply using Convert.ToChar().
If you don’t want to use Set Range Color, the other method involves a key combination to set the color from the Ribbon, which works well for me since the colors are shades that I like.