Pages in topic:   [1 2] >
Excel 2010: How to bind "change font to red" to a keyboard shortcut
Thread poster: Samuel Murray

Samuel Murray  Identity Verified
Netherlands
Local time: 08:21
Member (2006)
English to Afrikaans
+ ...
Dec 24, 2014

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


Direct link Reply with quote
 

Dan Lucas  Identity Verified
United Kingdom
Local time: 07:21
Member (2014)
Japanese to English
Tricky Dec 24, 2014

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


Direct link Reply with quote
 

Mikhail Zavidin
Ukraine
Local time: 09:21
English to Russian
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]


Direct link Reply with quote
 

Mikhail Zavidin
Ukraine
Local time: 09:21
English to Russian
In Excel this problem seems to be unsolvable Dec 24, 2014

Because you can't run any macro while you are in edit mode:

http://answers.microsoft.com/en-us/office/forum/office_2013_release-excel/vba-macro-to-highlight-selected-text-in-excel-cell/7b8acc6d-3bb9-498e-adb0-ab3cc67ce14c

Mikhail


Direct link Reply with quote
 

Samuel Murray  Identity Verified
Netherlands
Local time: 08:21
Member (2006)
English to Afrikaans
+ ...
TOPIC STARTER
@Dan Dec 24, 2014

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.


Direct link Reply with quote
 

Dan Lucas  Identity Verified
United Kingdom
Local time: 07:21
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


Direct link Reply with quote
 

Mikhail Zavidin
Ukraine
Local time: 09:21
English to Russian
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


Direct link Reply with quote
 

Samuel Murray  Identity Verified
Netherlands
Local time: 08:21
Member (2006)
English to Afrikaans
+ ...
TOPIC STARTER
@Dan Dec 24, 2014

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.


Direct link Reply with quote
 

Ben Senior  Identity Verified
Germany
Local time: 08:21
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.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


Direct link Reply with quote
 

Samuel Murray  Identity Verified
Netherlands
Local time: 08:21
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


Direct link Reply with quote
 

Ben Senior  Identity Verified
Germany
Local time: 08:21
German to English
Fixed? Dec 25, 2014

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


Direct link Reply with quote
 

Dominique Pivard  Identity Verified
Local time: 09:21
Finnish to French
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.


Direct link Reply with quote
 

Adrien Esparron
Local time: 08:21
Member (2007)
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]


Direct link Reply with quote
 

Ben Senior  Identity Verified
Germany
Local time: 08:21
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


Direct link Reply with quote
 

Emma Goldsmith  Identity Verified
Spain
Local time: 08:21
Member (2010)
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/


Direct link Reply with quote
 
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

Advanced search






Wordfast Pro
Translation Memory Software for Any Platform

Exclusive discount for ProZ.com users! Save over 13% when purchasing Wordfast Pro through ProZ.com. Wordfast is the world's #1 provider of platform-independent Translation Memory software. Consistently ranked the most user-friendly and highest value

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 »



Forums
  • All of ProZ.com
  • Term search
  • Jobs
  • Forums
  • Multiple search