Return Offset Value from Lookup

Hello all, I have a task which I would like to excelerize
I have atached an excel workbook with a mini example of my task, but basically ,

you search column H for "X", when found you look at the row preceeding it in the same column H (row Y) - go to Row Y's Column B, grab that, bring it to column C of Row X and also program column D as =(C#-B#) WHERE # is the corresponding row of the Activesheet of column A, or B they both have the same size., basically calculating the duration here.. I do not know how to program a row to do that automatically, I do one and then holt ctrl + drag mouse down ..


thanks for reading and heres the file, its got a complete example, before and after,

Does this do what you want?

Please Login or Register  to view this content.

Hi,

You don't necessarily need a macro. You can achieve this with functions and conditional formats.

In C2 enter

Please Login or Register  to view this content.

And use a conditional 'Formula Is' format in A2

Please Login or Register  to view this content.

and set the conditional font colour to white

Copy down as necessary. Set additional cond. formats in column B if you want red/blue colours.

HTH