1. ## Macro to find an adjacent cell (Excell 2002)

Huge picture that caused horizontal scrolling reduced in size - Mod

Hi

I think I need a Macro to highlight some cells in a column and then then select a non adjacent cell and place it into another cell.

Any help would be gratefully appreciated. Please see screen shot.

2. ## Re: Macro to find an adjacent cell (Excell 2002)

Will there always be an exact match, or do you want to stop as soon as the cumulative sum is at least as large as the value in C2?

3. ## Re: Macro to find an adjacent cell (Excell 2002)

Hi Hans

I does not need to be an exact match I do want to stop when the sum is at least as large a the value in C2

Regards

4. ## Re: Macro to find an adjacent cell (Excell 2002)

Click on the column letter D.
Select Insert | Column... (don't worry, we will hide this column later on.)
In cell D7, enter the formula =C7+0.0000000001
In cell D8, enter the formula =C8+D7
Fill cell D8 down as far as needed.
This creates a cumulative sum in D710 (or as far down as needed.)
In cell D3, enter the formula =MATCH(C2,D710,1) where D710 is the range you filled in the previous step.
This formula returns the index of the row we need.
In cell E3, enter the formula =OFFSET(D7,D3,2)
This formula looks up the unit price.
Click on the column letter D.
Select Format | Column | Hide.

5. ## Re: Macro to find an adjacent cell (Excell 2002)

Hans didn't answer about highlighting the cells. You can do this with conditional formatting:
Select QS7 to QS whatever
Format - cond format
formula is:
=SUM(\$QS\$7:QS7)<=\$C\$2
<format>
patterns tab (select color)
<ok><ok>

If the sum is <= the value in C2 they will be colored.

Steve

6. ## Re: Macro to find an adjacent cell (Excell 2002)

Hi Steve / Hans

I am deeply grateful to you both for your invaluable help.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•