[FONT="]Happy New Year to all.[/FONT]
[FONT="]I'm new here, and this is about Rollin_Again answer to Bibleuser question last July 27, 2012 [/FONT]([FONT="]Replace contents in brackets with Italicized words and certain font color).[/FONT]
[FONT="]The formula works to me but, when I run it, it takes away the brackets (in my case parentheses). What I want is change the font color and italicized including the brackets, so the brackets stay.[/FONT] I'm using Excel 2007
[FONT="]Hoping someone could rewrite the formula for me, I need it very importantly.[/FONT]
[FONT="]Thank you very much.[/FONT]
Here's the formula:
Sub FindBrackets()
Set vFound = Cells.Find(What:="[*]", _
After:=ActiveCell, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
If Not vFound Is Nothing Then
Do
Call FormatCell(vFound.Address)
Set vFound = Cells.FindNext(vFound)
Loop Until vFound Is Nothing
Else
MsgBox ("Not Found")
End If
End Sub
Sub FormatCell(vAddress As String)
Dim aArray() As String
vCount = 0
Do While InStr(1, Range(vAddress).Value, "[") > 0 Or InStr(1, Range(vAddress).Value, "]") > 0
ReDim Preserve aArray(vCount)
vStart = InStr(1, Range(vAddress).Value, "[") - 1
vEnd = InStr(1, Range(vAddress).Value, "]") - 1
Range(vAddress).Value = Replace(Range(vAddress).Value, "[", "", , 1)
Range(vAddress).Value = Replace(Range(vAddress).Value, "]", "", , 1)
aArray(vCount) = vStart & "," & vEnd
vCount = vCount + 1
Loop
For i = 0 To UBound(aArray)
vStart = CInt(Mid(aArray(i), 1, InStr(1, aArray(i), ",") - 1))
vEnd = CInt(Mid(aArray(i), InStr(1, aArray(i), ",") + 1))
Range(vAddress).Characters(Start:=vStart, Length:=(vEnd - vStart + 1)).Font.Color = -16776961
Range(vAddress).Characters(Start:=vStart, Length:=(vEnd - vStart + 1)).Font.FontStyle = "Italic"
Next i
End Sub
[FONT="]I'm new here, and this is about Rollin_Again answer to Bibleuser question last July 27, 2012 [/FONT]([FONT="]Replace contents in brackets with Italicized words and certain font color).[/FONT]
[FONT="]The formula works to me but, when I run it, it takes away the brackets (in my case parentheses). What I want is change the font color and italicized including the brackets, so the brackets stay.[/FONT] I'm using Excel 2007
[FONT="]Hoping someone could rewrite the formula for me, I need it very importantly.[/FONT]
[FONT="]Thank you very much.[/FONT]
Here's the formula:
Sub FindBrackets()
Set vFound = Cells.Find(What:="[*]", _
After:=ActiveCell, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
If Not vFound Is Nothing Then
Do
Call FormatCell(vFound.Address)
Set vFound = Cells.FindNext(vFound)
Loop Until vFound Is Nothing
Else
MsgBox ("Not Found")
End If
End Sub
Sub FormatCell(vAddress As String)
Dim aArray() As String
vCount = 0
Do While InStr(1, Range(vAddress).Value, "[") > 0 Or InStr(1, Range(vAddress).Value, "]") > 0
ReDim Preserve aArray(vCount)
vStart = InStr(1, Range(vAddress).Value, "[") - 1
vEnd = InStr(1, Range(vAddress).Value, "]") - 1
Range(vAddress).Value = Replace(Range(vAddress).Value, "[", "", , 1)
Range(vAddress).Value = Replace(Range(vAddress).Value, "]", "", , 1)
aArray(vCount) = vStart & "," & vEnd
vCount = vCount + 1
Loop
For i = 0 To UBound(aArray)
vStart = CInt(Mid(aArray(i), 1, InStr(1, aArray(i), ",") - 1))
vEnd = CInt(Mid(aArray(i), InStr(1, aArray(i), ",") + 1))
Range(vAddress).Characters(Start:=vStart, Length:=(vEnd - vStart + 1)).Font.Color = -16776961
Range(vAddress).Characters(Start:=vStart, Length:=(vEnd - vStart + 1)).Font.FontStyle = "Italic"
Next i
End Sub