Highlight column based on Q-period

[jarous]

Hi, could you please help how can I highlight column F to I based on Q period?
Now I need to highlight column H for Q3 period from row H11 to H17 and in January will need to do the same but for column I for Q4 period.
Could you please advise?

[kirankumar.mahanthi1 (Kirankumar Mahanthi)]

Hi,

We can go little dynamic way to achieve the requirement.

Use the below expression to get the Quarter month and year dynamically. Assign this expression to one string variable eg: QuarterMonthAndYear

QuarterMonthAndYear = “Q”+(Math.Floor(DateTime.Today.Month/4)+1).ToString+now.ToString(“yyyy”)

use this value as input for the lookup range activity to get the cell reference for where the column present in our case if we are running in January we will get the cell reference I column. Use the below docs to understand how to use lookup range activity.

LookUp Range

After getting the appropriate column we can use select range activity to select the appropriate cells in our case I11 to I17. use the below docs to understand on the select range functionality. that’s it with these steps we can achieve your problem. thanks.

Select Range

[jarous]

Hi and thank you for your advice. Lookup Range works fine I kept the result in Variable but not sure how to write Range with variable for rows. Would you be able to advice as well?

[kirankumar.mahanthi1 (Kirankumar Mahanthi)]

Hi,

Now it would come output like “H11” so we can use the variable is the input for write range activity.

we have use the below expression get the number alone from cell reference.

yourvariable.substring(1,CellRange.Length-1) - To get the number - CellValue

yourvariable.Substring(0,1) - To get the cell in our case H - Cellkey

increase the cell number by adding your count based on the how many cell you want to write.

CellValueFinal - Cint(CellValue+5)

put the above expressions in two different variables - suppose CellValue & Cellkey

our input should be if you are using write range or select range

range we have to provide → yourvariable+“:”+Cellkey+CellValueFinal.Tostring

please let me know if you did not get the above logic. thanks

[jarous]

Hi, actually variable came out as H9 as in that column I have Q3 2021 specification but I need to highlight in yellow from row H11 till H17. thank you I will check that and come back to you if something is not clear.