borders between 2 specific words

**pedy** · 07-02-2009, 08:56 AM

Hi all,

I have this bit of code that I need help with:

Please Login or Register  to view this content.

I am trying to is create borders around all cells in the range by looking for 2 specific words to begin and end the borders. Let's say word1 & word2. The border should begin after (under) word1 and end before (above) word2. It will also happen that there will be several occurrences of word1 & word 2 but it should only create the borders between word1 & word 2 for every occurrence of those words.

Now this code seems to works but only for 1 occurrence of word1 & word2 (from the bottom up) and all the others are ignored

Can someone help please ?

Pedy

**Phil_V** · 07-02-2009, 09:28 AM

Pedy,

Your .Find statements are using xlPrevious, which means that they search backwards through the range. This is the reason it is always the 'last' pair that get affected.
Also there is no looping in the macro above, (other than to colour & border the rows of a single pair), which is why it does not operate on any other pairs. Try the code below, I have tried to explain it in comments, but please ask if there is anything you are unsure about:

Please Login or Register  to view this content.

Incidently why do you have the macro in the selection change macro? This means it runs every time you move the cursor on the sheet, clearing all the formatting and redoing it...

**pedy** · 07-02-2009, 11:06 AM

That's great, thanks Phil!

I was using the selection change to make testing quicker.

Thanks agin :D

**Phil_V** · 07-02-2009, 11:10 AM

No worries. If this solves the case please mark the thread as Solved;

Edit your first post, click [Go Advanced].
In the prefix dropdown box select 'Solved'.

**pedy** · 07-08-2009, 11:35 AM

Hi again,

Would it be possible to insert another part to this code?

Before the borders routine, I would like to look for the highest value (number) in a whole column in all of the occurrences of the 'word1' & 'word2'. Once it returns the highest value it will use that value and enter a combination of the words “In” in the next cell (the cell next to ‘word1’) and “Out” in the cell next to the previous cell (that now has ‘In’). The highest value is used to enter that combination (“In” & “Out”) in the next cells.

Ex.: if we look in column B and the highest value is 5, and the ‘word1’ is in cell A3 then it will enter “In” in cell B3 & “Out” in cell C3. This is done for a total of 5 times (ten cells total).

Now the example will change a bit because before the loop of “In” & “Out”, the word “Out” must be entered once before the loop.

So the changed Ex would be:

if we look in column B and the highest value is 5, and the ‘word1’ is in cell A3 then it will begin by entering “Out” once in cell B3 and then loop “In” in cell C3 & “Out” in cell D3. This is done for a total of 5 times (eleven in total (ten cells for the loop + the 1st “Out”)).

I must also mention the column that we’re looking in to find the heist value contains stuff like “4 / 8 – 10” or “5 / 8 – 10 g”. And also the tables (occurrences of ‘word1’ & ‘word2’) are separated by empty rows.

I hope I have described it ok.

Pedy

**Phil_V** · 07-08-2009, 12:04 PM

Pedy, Are you able to upload an example workbook as this would make it much clearer.
Perhaps one sheet of example input data, and one sheet with an example of what you want as an output?

**pedy** · 07-08-2009, 12:55 PM

Before:

Please Login or Register  to view this content.

After:

Please Login or Register  to view this content.

In this example 7 is the highest value (1st number in the cell) found in the column so the paired 'In' 'Out' loop will be inserted 7 times (7 'In' and 7 'Out').

Pedy