0

I am looking for a way to apply conditional formatting to a column in Excel such that each time a certain text string is found it highlights the cell exactly 60 rows below it.

For example if Rows 1 & 20 have the text "Buy" then rows 61 & 80 should be highlighted in Green.

It needs to count cells even if they are blank

Improve this question

1 Answer 1

Reset to default
0

I have found the answer so posting it in case others have the same problem.

I have used a INDIRECT formula as follows:

=INDIRECT(ADDRESS(ROW()+X,COLUMN()+Y))=Z - Row()+X & Column+Y can be used to offset so in my case I want to check the cell 60 rows above so will use ROW()-60,COLUMN()

So I will use the following formula for my conditional format

=INDIRECT(ADDRESS(ROW()-60,COLUMN()))="Buy"

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .