Routine to remove brackets around words, leaving the format intact. Doesn't always work.

Thank you both. Yes, that worked p24.

Still curious to know why it worked most of the time, but not always. I thought Alvero was onto something there, but I'm sure there have been other times it hasn't worked when the first word wasn't bracketed.

Anyhow, it seems to be okay now, fingers crossed!

Thanks again to you both.

It suppose to color the words only if the original word in brackets are colored....

Change to this.

It will strip the words in brackets and color them in red.

Sub RmBrackets()
    Dim r As Range, x(), i As Long
    With CreateObject("VBScript.RegExp")
        .Global = True
        For Each r In Range("L22")
            If r.Address = r.MergeArea.Cells(1).Address Then
                .Pattern = "(\[)([^\[\]]+)(\])"
                If .test(r.Value) Then
                    ReDim x(1 To .Execute(r.Value).Count, 1 To 3)
                    For i = 0 To .Execute(r.Value).Count - 1
                        x(i + 1, 1) = .Execute(r.Value)(i).firstindex + 1 - i * 2
                        x(i + 1, 2) = .Execute(r.Value)(i).Length - 2
                        x(i + 1, 3) = r.Characters(.Execute(r.Value)(i) _
                            .firstindex + 2, 1).Font.Color
                    Next
                    r.Value = .Replace(r.Value, Chr(2) & "$2" & Chr(2))
                    .Pattern = Chr(2)
                    r.Value = .Replace(r.Value, "")
                    For i = 1 To UBound(x, 1)
                        r.Characters(x(i, 1), x(i, 2)).Font.Color = vbRed
                    Next
                End If
            End If
        Next
    End With
End Sub

OK, got it.

Thanks very much for your time.

Regards.

I don't understand what you are talking about and is this your thread?

No it's mine. And I do understand what he's talking about.

To go back to the beginning:

I had a routine that coloured anything between brackets (and the brackets as well) in red. The routine ColourBrackets does that fine.

I then needed to remove the brackets, LEAVING THE RED WORDS RED. That is the routine called RmBrackets, for which you supplied the code.

I have now discovered that there are times that it doesn't work, so I came back here and asked you, Jindon, if you could solve the problem.

In the meantime, p24 came up with a patch, which I tried, and it worked fine. So far I haven't been able to trip it up.

You then came along with an updated version of your original code. I used that, and even with the bad sentence I supplied in the test book, it didn't work for me, and it obviously didn't for p24, which is why he mentioned it.

So we have your original code, which didn't work in all cases, and the updated version, which still doesn't.
And in both cases, it does work (so far!) with p24's patch included.


However, with the patch it does work, so I'm happy.

Would still be curious to know why the bracket at the beginning trips it up.

Regards to you both.

Can you show me the example of what you are talking.

I can not replicate the situation.

Ok, well there's obviously no point sending you the workbook because you already have it!

So all I can do is supply a picture of what's happening, which I have done.

The question now becomes, then, why does this happen to ap24 and me, but not to you?

Mysterious!

Regards.

As I said I have no idea about what you are saying.

It is working to me.

NOW I UNDERSTAND.

Now I have back your version of my test book I see that you are not doing the same thing I am!

I am using two routines. You are using one routine - the one you wrote - and ignoring the previous one.

The way I did it was:

Routine 1.
ColourBrackets. Take the words in brackets and colour them. (So we finish with a sentence where the words are two different colours.)
Routine 2.
RmBrackets. Strip the brackets, and leave the red words red.

What you are doing:

Routine 1. RmBrackets
Take a sentence where all the words start same colour (ignoring Routine 1), and remove brackets, making any words between them red. So you are not leaving words red, you are making them red.

That is why ap24's patch worked, it made the sentence the colour your routine needs, rather than two different colours.

And here's the good bit: if I leave out the previous routine too, your routine works!

SO you've saved me a routine, which will speed things up nicely, and also the mystery is solved.

Thank you.

OK, you seems to stick with the other routine...

Glad we finally come to the end.