| Pages in topic: [1 2] > | Excel 2010: How to bind "change font to red" to a keyboard shortcut Thread poster: Samuel Murray
|
|---|
Samuel Murray 
Netherlands
Local time: 11:54
Member (2006)
English to Afrikaans
+ ...
Hello everyone
I want to be able to select any text in a cell (in Excel 2010), and then change its colour to red, using a keyboard shortcut only. I don't want to change the entire cell to red, but only the text that I have selected, within the cell. Does anyone know how to set up such a shortcut?
My file has hundreds of those, and since Find/Replace doesn't work in Excel (I mean, I can't tell Find/Replace to find XYZ in a cell and make XYZ red, without making the enti... See more Hello everyone
I want to be able to select any text in a cell (in Excel 2010), and then change its colour to red, using a keyboard shortcut only. I don't want to change the entire cell to red, but only the text that I have selected, within the cell. Does anyone know how to set up such a shortcut?
My file has hundreds of those, and since Find/Replace doesn't work in Excel (I mean, I can't tell Find/Replace to find XYZ in a cell and make XYZ red, without making the entire cell red), my next best option is to select each instance and then change the font colour to red manually... by a keyboard shortcut.
Thanks
Samuel ▲ Collapse
| | | | Dan Lucas
United Kingdom
Local time: 10:54
Member (2014)
Japanese to English
Samuel Murray wrote:
I want to be able to select any text in a cell (in Excel 2010), and then change its colour to red, using a keyboard shortcut only. I don't want to change the entire cell to red, but only the text that I have selected, within the cell.
Your requirement to change only the selected text makes this difficult. Changing all the text in a specific cell red is easy enough with a VBA macro:
Sub FontMakeRed()
With Selection.Font
.ColorIndex = 3
End With
End Sub
But that won't work here - at least in Excel 2007. Usually you would use something like:
With ActiveCell.Characters(Start:=4, Length:=3).Font
...but we don't know in advance the positions of this string for which you are searching.
Can you tell us a bit more about the problem? Is it one string you are looking for or many different strings? Multiple worksheets? Multiple files?
Dan
| | | | | Through Word | Dec 24, 2014 |
Hi Samuel.
At first glance the task sounds like easy.
But it is not. My second thought was a workaround through Word. In Word this shortcut is solvable in a moment. Just copy/paste in Word, do the job, and copy/paste back to Excel if data dont change.
Mikhail.
[Редактировалось 2014-12-24 17:52 GMT]
| | | | |
|
|
|
Samuel Murray
Netherlands
Local time: 11:54
Member (2006)
English to Afrikaans
+ ...
TOPIC STARTER
Dan Lucas wrote:
Usually you would use something like:
With ActiveCell.Characters(Start:=4, Length:=3).Font
...but we don't know in advance the positions of this string for which you are searching.
Hmm... interesting. Is it possible to run such a macro on the fly, i.e. not by first saving it and then running it via the dialog or via a keyboard shortcut? If so, then I could simply create a batch file with lines of such macros, each line being customised for the particular cell.
Can you tell us a bit more about the problem? Is it one string you are looking for or many different strings? Multiple worksheets? Multiple files?
One file, one worksheet, one column, hundreds of cells. All the text that should be red are "HTML" tags. The non-HTML content should be black. For reasons that I can't mention, doing this in MS Word and then pasting into Excel is not an option -- the colouration must be done in Excel itself.
| | | | Dan Lucas
United Kingdom
Local time: 10:54
Member (2014)
Japanese to English
| Sounds doable | Dec 24, 2014 |
Samuel Murray wrote:
Hmm... interesting. Is it possible to run such a macro on the fly, i.e. not by first saving it and then running it via the dialog or via a keyboard shortcut? If so, then I could simply create a batch file with lines of such macros, each line being customised for the particular cell.
Well, that would negate the advantage of automation. Better to have one macro that looks for a list of values to color, then skips through all the cells in the column in search of them. There can't be that many tags, right? The program flow would be something like (in pseudo-code)
set the counter X to 1
set Y to 1000 ' assuming you have 1000 cells
begin loop
check text in column 1 row X
does it contain a tag?
if yes, colour the text red
if X is equal to Y exit loop
otherwise
increment counter by 1
goto beginning of loop
The key issue would be getting a list of tags used. Or, alternatively, we could just colour anything between angle brackets, since we know that all valid HTML tags begin and end with angle brackets. A simplifying assumption but probably safe.
Best if you could mock up a sample of, say, 5 cells containing tags. Then we could test and see.
Dan
| | | | | Here knowing of regex could help | Dec 24, 2014 |
The search algorithm in the macro should contain pattern string like: </?[a-z][a-z0-9]*[!<>]*>. This is in the language of the Word's regular expressions. This finds all htmls.
Mikhail
| | | | Samuel Murray
Netherlands
Local time: 11:54
Member (2006)
English to Afrikaans
+ ...
TOPIC STARTER
Dan Lucas wrote:
Better to have one macro that looks for a list of values to color, then skips through all the cells in the column in search of them.
That would be nice, but having a macro that simply colours the current segment would also be useful, for one can bind that macro to a keyboard shortcut and execute it when necessary (e.g. directly after editing or translating a segment, or when not all cells should be coloured).
There can't be that many tags, right?
In my case, some cells have no tags, and some cells have about 10 tags. They're HTML-like tags, i.e. starting with < and ending with >.
...does it contain a tag?
if yes, colour the text red...
I'm afraid my macro skills are nowhere near that (-: and as far as I can tell, Excel can't do wildcard/regex find/replace (not via the Find/Replace dialog, anyway), so I wouldn't even begin to know how to tell a macro "this is a tag".
I think a macro as described would be quite useful for many translators working in Excel. I'll send you a sample privately.
| | |
|
|
|
Ben Senior
Germany
Local time: 11:54
German to English
| Colour Tag Text | Dec 25, 2014 |
Hi Samuel,
The VBA macro below will colour all of the tag text and the associated angle brackets in every cell in use red. It will also handle multiple tags in the same cell.
Sub ColourTagsRed()
Dim cell As Range
Dim sText As String
Dim iLen As Integer
Dim c As Integer
Dim iStart As Integer
Dim iEnd As Integer
Dim sChar As String
For Each cell In ActiveSheet.UsedRange.C... See more Hi Samuel,
The VBA macro below will colour all of the tag text and the associated angle brackets in every cell in use red. It will also handle multiple tags in the same cell.
Sub ColourTagsRed()
Dim cell As Range
Dim sText As String
Dim iLen As Integer
Dim c As Integer
Dim iStart As Integer
Dim iEnd As Integer
Dim sChar As String
For Each cell In ActiveSheet.UsedRange.Cells
sText = CStr(cell.Value)
iLen = Len(sText)
For c = 1 To iLen
sChar = Mid(sText, c, 1)
If sChar = "" Then
iEnd = c + 1
cell.Characters(Start:=iStart, Length:=(iEnd - iStart)).Font.Color = -16776961
End If
Next c
Next cell
End Sub
If you run the macro at the start you should not need to mark any of the text and then change its colour.
I hope it helps you with your problem.
Regards
Ben ▲ Collapse
| | | | Samuel Murray
Netherlands
Local time: 11:54
Member (2006)
English to Afrikaans
+ ...
TOPIC STARTER | @Tyke, fixed | Dec 25, 2014 |
Tyke wrote:
...
Thanks to ProZ.com's HTML forum bug, your macro got broken. Here's the fixed version:
Sub ColourTagsRed()
Dim cell As Range
Dim sText As String
Dim iLen As Integer
Dim c As Integer
Dim iStart As Integer
Dim iEnd As Integer
Dim sChar As String
For Each cell In ActiveSheet.UsedRange.Cells
sText = CStr(cell.Value)
iLen = Len(sText)
For c = 1 To iLen
sChar = Mid(sText, c, 1)
If sChar = "<" Then
iStart = c
End If
If sChar = ">" Then
iEnd = c + 1
cell.Characters(Start:=iStart, Length:=(iEnd - iStart)).Font.Color = -16776961
End If
Next c
Next cell
End Sub
| | | | Ben Senior
Germany
Local time: 11:54
German to English
Hi Samuel,
Sorry I don't understand, but what got fixed? Your fixed version looks exactly like my original. Did it help with your problem?
Regards,
Ben
| | | | | Characters missing | Dec 26, 2014 |
Tyke wrote:
Sorry I don't understand, but what got fixed? Your fixed version looks exactly like my original.
Not exactly: some characters, namely < and >, were missing, due to (as Samuel said) a long-time shortcoming in ProZ forums.
| | |
|
|
|
Platary (X)
Local time: 11:54
German to French
+ ...
| Could we add something ? | Dec 26, 2014 |
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]
| | | | Ben Senior
Germany
Local time: 11:54
German to English
| Missing characters | Dec 26, 2014 |
Hi Dominique,
Thanks for the explanation, I was not aware of that.
But for future reference how did you and Samuel get round the problem? You both managed to get the angle brackets in your messages.
Regards
Ben
| | | | Emma Goldsmith
Spain
Local time: 11:54
Member (2004)
Spanish to English
| angle brackets in forum posts | Dec 26, 2014 |
Tyke wrote:
But for future reference how did you and Samuel get round the problem? You both managed to get the angle brackets in your messages.
For < type "& lt" (without the space after &)
For > type "& gt" (without the space after &)
More details here:
http://multifarious.filkin.com/2014/07/01/disappearing-forum-posts/
| | | | | 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 | CafeTran Espresso | You've never met a CAT tool this clever!
Translate faster & easier, using a sophisticated CAT tool built by a translator / developer.
Accept jobs from clients who use Trados, MemoQ, Wordfast & major CAT tools.
Download and start using CafeTran Espresso -- for free
Buy now! » |
| | TM-Town | Manage your TMs and Terms ... and boost your translation business
Are you ready for something fresh in the industry? TM-Town is a unique new site for you -- the freelance translator -- to store, manage and share translation memories (TMs) and glossaries...and potentially meet new clients on the basis of your prior work.
More info » |
|
| | | | X Sign in to your ProZ.com account... | | | | | |
Login to reply/comment 
