| Pages in topic: < [1 2] | Excel 2010: How to bind "change font to red" to a keyboard shortcut Thread poster: Samuel Murray
|
|---|
Ben Senior 
Germany
Local time: 14:41
German to English
| Blue text between the tags | Dec 26, 2014 |
Adrien Esparron wrote:
Hi all, and thanks Tyke for a very interesting macro. I had something without iteration and your solution is a good one. I'm now just wondering if we could add something, for instance changing the color text between the tags (i.e. tags in red like now and text in blue for instance between opening and closing tags). Which were your idea, if any ?
Thanks in advance.
Adrien
[Modifié le 2014-12-26 09:13 GMT]
Hi Adrien,
In principle it is perfectly feasible, but I'm not entirely sure I fully understand what you are wanting. Do you mean something like a B in angle brackets to switch on bold text to be in red, the text that should be bold in blue and the /B in angle brackets to switch off bold text to be also red? What should be done if a closing tag is missing? Some tags are also in square brackets and these can easily be dealt with by modifying my macro by replacing the angle brackets with square brackets.
Regards
Ben
| | | | Ben Senior
Germany
Local time: 14:41
German to English
| Words of wisdom... | Dec 26, 2014 |
Emma Goldsmith wrote:
For < type "& lt" (without the space after &)
For > type "& gt" (without the space after &)
Thanks for that Emma, it's easy when you know how ;-}
Regards
Ben
| | | | Samuel Murray
Netherlands
Local time: 14:41
Member (2006)
English to Afrikaans
+ ...
TOPIC STARTER | @Adrian and @Tyke | Dec 26, 2014 |
Adrien Esparron wrote:
I'm now just wondering if we could add something, for instance changing the color text between the tags (i.e. tags in red like now and text in blue for instance between opening and closing tags). Which were your idea, if any?
Ooh, that would be difficult, I imagine, because some tag pairs are nested, so you wouldn't be able to use a simple macro to determine which text is "inside" a tag pair and which isn't. Even in MS Word it would be a macro that would work only sometimes.
Tyke wrote:
Did it help with your problem?
Yes, it's perfect, thanks. I just edited the macro a bit so that it only works on the active cell (and then I bound a keyboard shortcut to it, so that I can execute it easily). I'll tinker with it later so that it only works on the current column, or only on selected cells, which would be more useful to me, but it's quite useful to me already. Thanks a lot.
[Edited at 2014-12-26 12:02 GMT]
| | | | | Here is a little bit another macro | Dec 26, 2014 |
Hi Adrien.
Adrien Esparron wrote:
Hi all, and thanks Tyke for a very interesting macro. I had something without iteration and your solution is a good one. I'm now just wondering if we could add something, for instance changing the color text between the tags (i.e. tags in red like now and text in blue for instance between opening and closing tags). Which were your idea, if any ?
Adrien
[Modifié le 2014-12-26 09:13 GMT]
Following is exactly what you want:
Public Sub ColourHtmlTags()
Dim pattern As String: pattern = "</?[a-z][a-z0-9]*[^<>]*>"
Dim regEx As New RegExp
Dim colMatches As MatchCollection
Dim strInput As String
Dim Myrange As Range
Set Myrange = Selection
For Each cell In Myrange
If pattern "" Then
strInput = cell.Text
With regEx
.Global = True
.MultiLine = True
.IgnoreCase = True
.pattern = pattern
End With
If regEx.Test(strInput) Then
Set colMatches = regEx.Execute(strInput)
For Each Match In colMatches
Dim length As Long: length = Len(Match.Value)
cell.Characters(Start:=Match.FirstIndex + 1, length:=length).Font.Color = vbRed
cell.Characters(Start:=Match.FirstIndex + 2, length:=length - 2).Font.Color = vbBlue
Next
End If
End If
Next
End Sub
It recognizes html tags more precisely.
The only two things should be done before run it is:
1) In MS Visual Basic Editor menu "Tools" click "References" check the box next to "Microsoft VBScript Regular Expressions 5.5". Then click "OK".
2) In worksheet select the range to work on and run the above macro.
[Редактировалось 2014-12-26 15:31 GMT]
[Редактировалось 2014-12-26 15:35 GMT]
| | |
|
|
|
Ben Senior
Germany
Local time: 14:41
German to English
| @Adrien and @Samuel | Dec 26, 2014 |
Hi Adrien and Samuel,
Have a look at this amended macro and see if it fits your purposes. It assumes that all tags are enclosed in angle brackets and that the second character of a closing tag is a slash character. Let's see if Emma's tip worked her on the angle bracket.
Sub NestedRedTagsBlueText()
Dim cell As Range
Dim sText As String
Dim sChar As String
Dim iLen As Integer
Dim c As Integer
Dim iSta... See more Hi Adrien and Samuel,
Have a look at this amended macro and see if it fits your purposes. It assumes that all tags are enclosed in angle brackets and that the second character of a closing tag is a slash character. Let's see if Emma's tip worked her on the angle bracket.
Sub NestedRedTagsBlueText()
Dim cell As Range
Dim sText As String
Dim sChar As String
Dim iLen As Integer
Dim c As Integer
Dim iStart As Integer
Dim iEnd As Integer
Dim iStartTagText As Integer
Dim iEndTagText As Integer
Dim bTagTextStarted As Boolean
Dim bClosingTag As Boolean
For Each cell In ActiveSheet.UsedRange.Cells
sText = CStr(cell.Value)
iLen = Len(sText)
bTagTextStarted = False
bClosingTag = False
For c = 1 To iLen
sChar = Mid(sText, c, 1)
If sChar = "<" Then
If Mid(sText, c + 1, 1) = "/" _
And bTagTextStarted = True Then
bClosingTag = True
iEndTagText = c
bTagTextStarted = False
cell.Characters(Start:=iStartTagText, Length:=(iEndTagText - iStartTagText)).Font.ColorIndex = 5
Else
bClosingTag = False
End If
iStart = c
End If
If sChar = ">" Then
iEnd = c + 1
cell.Characters(Start:=iStart, Length:=(iEnd - iStart)).Font.ColorIndex = 3
If bClosingTag = False Then
iStartTagText = c + 1
bTagTextStarted = True
End If
End If
Next c
Next cell
End Sub ▲ Collapse
| | | | Platary (X)
Local time: 14:41
German to French
+ ...
| @ Tyke and Mikhail | Dec 27, 2014 |
Thanks a lot for your contributions ! Very good solutions. I'm not a VB expert, but I'm able to read and understand coding, and when necessary to correct it (adapt it).
I think, but I'm not sure, that there are some syntax questions with regular expressions depending of Office versions and applications.
Happy new Year !
Adrien
| | | | | Sorry -- some typos | Dec 27, 2014 |
Now it is fixed and checked:
Public Sub ColourHtmlTags()
Dim pattern As String: pattern = "</?[a-z][a-z0-9]*[^<>]*>"
Dim regEx As New RegExp
Dim colMatches As MatchCollection
Dim strInput As String
Dim Myrange As Range
Set Myrange = Selection
For Each cell In Myrange
If pattern <> "" Then
strInput = cell.Text
With regEx
... See more Now it is fixed and checked:
Public Sub ColourHtmlTags()
Dim pattern As String: pattern = "</?[a-z][a-z0-9]*[^<>]*>"
Dim regEx As New RegExp
Dim colMatches As MatchCollection
Dim strInput As String
Dim Myrange As Range
Set Myrange = Selection
For Each cell In Myrange
If pattern <> "" Then
strInput = cell.Text
With regEx
.Global = True
.MultiLine = True
.IgnoreCase = True
.pattern = pattern
End With
If regEx.Test(strInput) Then
Set colMatches = regEx.Execute(strInput)
For Each Match In colMatches
Dim length As Long: length = Len(Match.Value)
cell.Characters(Start:=Match.FirstIndex + 1, length:=length).Font.Color = vbRed
cell.Characters(Start:=Match.FirstIndex + 2, length:=length - 2).Font.Color = vbBlue
Next
End If
End If
Next
End Sub
It recognizes html tags more precisely.
The only two things should be done before run it is:
1) In MS Visual Basic Editor menu "Tools" click "References" check the box next to "Microsoft VBScript Regular Expressions 5.5". Then click "OK".
2) In worksheet select the range to work on and run the above macro. ▲ Collapse
| | | | | Pages in topic: < [1 2] | To report site rules violations or get help, contact a site moderator: You can also contact site staff by submitting a support request » Excel 2010: How to bind "change font to red" to a keyboard shortcut | Trados Business Manager Lite | Create customer quotes and invoices from within Trados Studio
Trados Business Manager Lite helps to simplify and speed up some of the daily tasks, such as invoicing and reporting, associated with running your freelance translation business.
More info » |
| | Protemos translation business management system | Create your account in minutes, and start working! 3-month trial for agencies, and free for freelancers!
The system lets you keep client/vendor database, with contacts and rates, manage projects and assign jobs to vendors, issue invoices, track payments, store and manage project files, generate business reports on turnover profit per client/manager etc.
More info » |
|
| | | | X Sign in to your ProZ.com account... | | | | | |
Login to reply/comment 
