I need a simple excel macro and I'm willing to pay.

March 24, 2015 5:23 AM

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.

posted by NoiselessPenguin to Computers & Internet (13 answers total)

Best answer: You might try asking the people over at StackOverflow and Super User. StackExchange is like AskMetafilter, but they focus on specific topics. Overflow is programming, Super User is where Microsoft Office questions go.

When I googled which stack would be best for Excel, this is what I found:

For general Excel questions, including those dealing with built in Excel features, functions, and formulas, Super User is absolutely the correct site.

But for help with automation with Excel using VBA, including UDF (User Defined Functions), then Stack Overflow is probably a better resource
source

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]

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]

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

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]

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]

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

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

"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]

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

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

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

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.

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