I need a simple excel macro and I'm willing to pay.
March 24, 2015 5:23 AM Subscribe
I often (upwards of 15 times a day) need to select some of the text within a cell or textbox in Excel, and format it bold and red. Not all the text in the cell/box -- just the text I select. Is it possible to write macros to do this task? And where should I go to find someone to write it?
Here is an image of the before / after. http://imgur.com/fRlHl8Y
The text that needs red formatting doesn't follow a pattern that could be picked up with an expression or what not, and I need to be able to activate the macro with a keyboard shortcut.
Any advice on finding a VBA programmer to bash this out would be great.
Here is an image of the before / after. http://imgur.com/fRlHl8Y
The text that needs red formatting doesn't follow a pattern that could be picked up with an expression or what not, and I need to be able to activate the macro with a keyboard shortcut.
Any advice on finding a VBA programmer to bash this out would be great.
I could write you a macro.
The text that you want to bold and turn red... is it always the same text? Is the text that's in the entire cell always the same or is it variable?
posted by JimBJ9 at 5:38 AM on March 24, 2015 [1 favorite]
The text that you want to bold and turn red... is it always the same text? Is the text that's in the entire cell always the same or is it variable?
posted by JimBJ9 at 5:38 AM on March 24, 2015 [1 favorite]
If it's the last action you did, you can repeat the action by hitting Cmd and Y on a Mac, or hitting F4 on a PC.
In the latest versions of Excel, you can also create user-defined styles. These can be applied to the whole cell only though.
posted by Happy Dave at 5:39 AM on March 24, 2015
In the latest versions of Excel, you can also create user-defined styles. These can be applied to the whole cell only though.
posted by Happy Dave at 5:39 AM on March 24, 2015
Best answer: For what it's worth, AutoHotKey is a pretty easy to learn tool that I've come to rely on for creating all kinds of macros and shortcuts. I'm definitely NOT a coder, but they've made it VERY easy to create some pretty usable scripts within minutes of running the program for the first time.
posted by richmondparker at 5:52 AM on March 24, 2015 [2 favorites]
posted by richmondparker at 5:52 AM on March 24, 2015 [2 favorites]
Best answer: If you're editing/selecting text in a cell, you're in Edit Mode. Macros cannot be launched when Excel is in Edit Mode.
Would it be acceptable instead to indicate which text should be red using some kind of marker, and then run a macro later to convert marked text into red?
For instance:
This is my data and I want {{THIS TEXT}} bold and red.
posted by blue t-shirt at 5:54 AM on March 24, 2015 [2 favorites]
Would it be acceptable instead to indicate which text should be red using some kind of marker, and then run a macro later to convert marked text into red?
For instance:
This is my data and I want {{THIS TEXT}} bold and red.
posted by blue t-shirt at 5:54 AM on March 24, 2015 [2 favorites]
You can't launch a macro in Edit Mode, but you can run a macro that will make those changes based on character number and length. I just wrote one to do just that. It all depends on whether the text is variable.
posted by JimBJ9 at 5:58 AM on March 24, 2015
posted by JimBJ9 at 5:58 AM on March 24, 2015
If it's always the same text (or could be) you can use conditional formatting.
posted by chesty_a_arthur at 6:07 AM on March 24, 2015
posted by chesty_a_arthur at 6:07 AM on March 24, 2015
"The text that needs red formatting doesn't follow a pattern that could be picked up with an expression or what not. "
So obviously not the same text each time. It's in the question.
posted by blue t-shirt at 6:09 AM on March 24, 2015 [3 favorites]
So obviously not the same text each time. It's in the question.
posted by blue t-shirt at 6:09 AM on March 24, 2015 [3 favorites]
EDIT: And I just saw your mention that the text doesn't follow a pattern. Reading comprehension failure. In that case, I've got nothing. But I'll leave my original post here if you want to work off of it. Sorry!
Anyway, assuming that the string of text that you want to make bold and red (let's call it "THIS TEXT") is always the same, then this macro will do what you need.
---
Sub red_and_bold()
red_bold_text = "THIS TEXT"
start_num = WorksheetFunction.Find(red_bold_text, ActiveCell, 1)
With ActiveCell.Characters(Start:=start_num, Length:=Len(red_bold_text)).Font
.FontStyle = "Bold"
.Color = -16776961
End With
End Sub
---
So, you put that in your macro list, assign it to a hot key, go to the cell you need to change, hit your hot key, and all should be good.
posted by JimBJ9 at 6:25 AM on March 24, 2015
Anyway, assuming that the string of text that you want to make bold and red (let's call it "THIS TEXT") is always the same, then this macro will do what you need.
---
Sub red_and_bold()
red_bold_text = "THIS TEXT"
start_num = WorksheetFunction.Find(red_bold_text, ActiveCell, 1)
With ActiveCell.Characters(Start:=start_num, Length:=Len(red_bold_text)).Font
.FontStyle = "Bold"
.Color = -16776961
End With
End Sub
---
So, you put that in your macro list, assign it to a hot key, go to the cell you need to change, hit your hot key, and all should be good.
posted by JimBJ9 at 6:25 AM on March 24, 2015
It s been a while since I've made any macros, but I think you can do what JimBJ9 suggests, except instead of telling Excel to format the selected cell, you tell it to format the selected characters.
posted by notyou at 6:39 AM on March 24, 2015
posted by notyou at 6:39 AM on March 24, 2015
Response by poster: (No the text to be highlighted doesn't have a strict format -- it's file names / worksheet references).
Thanks for helping me clarify the question. I had the impression from Googling around that it was very hard to format selected text, but I did not know about 'Edit mode'!
I asked over on stack overflow about a find/replace macro for all text within {{ }}.
(The AutoHotKey page is a goldmine of interesting ideas and is worth a lot of further attention when I have the chance -- fascinating system wide automation!)
Edit:: and on preview blue t-shirt has written something that looks promising -- thanks a million!
posted by NoiselessPenguin at 7:34 AM on March 24, 2015
Thanks for helping me clarify the question. I had the impression from Googling around that it was very hard to format selected text, but I did not know about 'Edit mode'!
I asked over on stack overflow about a find/replace macro for all text within {{ }}.
(The AutoHotKey page is a goldmine of interesting ideas and is worth a lot of further attention when I have the chance -- fascinating system wide automation!)
Edit:: and on preview blue t-shirt has written something that looks promising -- thanks a million!
posted by NoiselessPenguin at 7:34 AM on March 24, 2015
Best answer: This macro will solve the variation of the problem that I described above. If you can tag the parts to be highlighted with {{double curly brackets}}, you can select a cell (or several cells) and this macro will replace those brackets with the styles you mentioned. Thanks to JimBJ9 for demonstrating how to format part of a cell.
posted by blue t-shirt at 7:35 AM on March 24, 2015 [5 favorites]
Sub ReddenStuff() Const startSymbol As String = "{{" Const endSymbol As String = "}}" Dim starts() As Integer Dim lengths() As Integer Dim numMatches numMatches = 0 For Each cell In Selection Dim startPos startPos = 1 Dim endPos 'look for the start symbol within the cell startPos = InStr(startPos, cell.Text, startSymbol, vbTextCompare) 'do this every time we find a new start symbol Do While startPos > 0 'find end symbol endPos = InStr(startPos, cell.Text, endSymbol, vbTextCompare) If endPos > 0 Then 'remember where we found the match and its size ReDim Preserve starts(numMatches) ReDim Preserve lengths(numMatches) starts(numMatches) = startPos lengths(numMatches) = (endPos - startPos) - Len(startSymbol) numMatches = numMatches + 1 'delete the start and end symbol cell.Value = Left(cell.Value, endPos - 1) & Mid(cell.Value, endPos + Len(endSymbol)) cell.Value = Left(cell.Value, startPos - 1) & Mid(cell.Value, startPos + Len(startSymbol)) End If 'find the next match startPos = InStr(startPos + 1, cell.Text, startSymbol, vbTextCompare) Loop For i = 0 To numMatches - 1 With cell.Characters(Start:=starts(i), Length:=lengths(i)).Font .FontStyle = "Bold" .Color = -16776961 End With Next Next End Sub
posted by blue t-shirt at 7:35 AM on March 24, 2015 [5 favorites]
Am I totally missing something here? You are actively selecting each bit of text one at a time as you work, right? This is not some kind of data load where you want to change it all at once?
Why do you not just hit the BOLD button and then the Text color button at that time? For me, that would be much easier than inserting 2 curly brackets....
(edit text in cell)(put mouse at start of text) (shift){{ (cursor or mouse to end of text) (shift)}} then go back later and change all to Red.
vs
(edit text in cell)(select text) (click Bold)(click TextRed)
with the possible addition of (Home) at the beginning of the sequence to get to the home menu on the ribbon, and the other possible addition of changing the Text color button to red if you used it for some other color in the meantime.
posted by CathyG at 6:24 AM on March 25, 2015
Why do you not just hit the BOLD button and then the Text color button at that time? For me, that would be much easier than inserting 2 curly brackets....
(edit text in cell)(put mouse at start of text) (shift){{ (cursor or mouse to end of text) (shift)}} then go back later and change all to Red.
vs
(edit text in cell)(select text) (click Bold)(click TextRed)
with the possible addition of (Home) at the beginning of the sequence to get to the home menu on the ribbon, and the other possible addition of changing the Text color button to red if you used it for some other color in the meantime.
posted by CathyG at 6:24 AM on March 25, 2015
This thread is closed to new comments.
When I googled which stack would be best for Excel, this is what I found: I'm always posting questions to Overflow about JavaScript and jQuery. The users there can be a little.. snobbish. But that's improving! As the site gets more popular, they seem to be getting nicer.
Just go over there and say "hey I need to do this. Can this be done with a macro? If so, how would I go about creating that macro?" - a good portion of the time a bored programmer will write it for you.
posted by royalsong at 5:37 AM on March 24, 2015 [1 favorite]