Highlight column based on Q-period

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?
image

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.

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.

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?

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

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.

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