VBA code to change specific text in a cell's results that contains a formula to bold and change the color

R

rbe100377

New Member
Joined
Apr 18, 2011
Messages
5
I would greatly appreciate anyone who will work on this issue with me. I have scoped the internet for a few days to see if someone else has been facing the same issue, and while there have been a few, I can't change the code around given to them so that it will work for me, as the situations have not been exactly the same.<?xml:namespace prefix = v ns = "urn:schemas-microsoft-com:vml" /><v:shapetype id=_x0000_t75 stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" o:preferrelative="t" o:spt="75" coordsize="21600,21600"> :oops:<v:stroke joinstyle="miter"></v:stroke><v:formulas><v:f eqn="if lineDrawn pixelLineWidth 0"></v:f><v:f eqn="sum @0 1 0"></v:f><v:f eqn="sum 0 0 @1"></v:f><v:f eqn="prod @2 1 2"></v:f><v:f eqn="prod @3 21600 pixelWidth"></v:f><v:f eqn="prod @3 21600 pixelHeight"></v:f><v:f eqn="sum @0 0 1"></v:f><v:f eqn="prod @6 1 2"></v:f><v:f eqn="prod @7 21600 pixelWidth"></v:f><v:f eqn="sum @8 21600 0"></v:f><v:f eqn="prod @7 21600 pixelHeight"></v:f><v:f eqn="sum @10 21600 0"></v:f></v:formulas><v:path o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"></v:path><?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:lock aspectratio="t" v:ext="edit"></o:lock></v:shapetype><o:p></o:p>
<o:p></o:p>
So, here is the issue I am having...<o:p></o:p>
<o:p></o:p>
Cells A37 and A44 have formulas in them in which the cell's results are text. Within the text are important names that need to stand out in Bold and the Color 45. For instance, Cell A37 might result in the text that says, "The monkey, Curious George, is mischievious." and cell A44 might say, "The elephant, Dumbo, used his ears to fly away.", and as I have shown, it needs to show-up this way in the spreadsheet.<o:p></o:p>
<o:p></o:p>
Conditional Formatting will not help as it will bold and color all the text produced by the formula, and I need only partial text (a few names)changed within the resulting text.

I've thought of a couple of options to go off of, I just don't know how to put them in Macro mode...
<o:p></o:p>
1)<o:p></o:p>
The names that will be interchanged within the resulting text will be different, different number of characters, and other cells will be referencing them, so I need the integrity of the formulas within these cells to remain unchanged. In otherwords, not to change from a formula to a value...unless somewhere in the code it can be changed back to keep the new formatting.<o:p></o:p>

OR
<o:p></o:p>
2)
Is there a way to build a macro, maybe, to insert the Names produced by the formulas to be built into the macro into the positions within the text strings (after a certain character position?) that I need? Then I could, instead of having the entire text in these merged cells as a formula, I could just paste my text as a value, and the macro could insert the formatted names in the places they belong within the text using a devised formula inside the macro?

I keep getting the Error
"Fatal error: Maximum execution time of 30 seconds exceeded in /usr/www/users/mrexcel/forum/includes/functions_newpost.php on line 260" each time I try to post, so I will try to explain the spreadsheet set-up as best as I can.

The first page of formula cells goes from A to AK. The second page goes from AN to BW. They are in the same tab in my workbook.

The cells that this is effecting are described below.

Cells A37:AO42 are merged and contain a formula
=AN28&$AN$32&" "&AN36
**Here AN32 refers to a Name that will need the bold and color**
AN28 and AN36 are straight text

Cells A44:AO55 are merged and contain a formula
=AN44&", "&$AN$40&", "&AN48
**Here AN40 refers to a Name (which is also a cell containing a formula as it is dependant on other information in the preceeding cells) that will need the bold and color**
AN44 and AN48 are straight text

A23 contains the formula
=IF('Levers MC^2'!CI2="Tracked","Dear "&INDEX('Levers MC^2'!A:CL,MATCH("Tracked",'Levers MC^2'!CI:CI,0)+AN14,68)&",",IF('Levers MC^2'!CI2="Targeted","Dear "&INDEX('Levers MC^2'!A:CL,MATCH("Targeted",'Levers MC^2'!CI:CI,0),68)&",",IF('Levers MC^2'!CI2="Guaranteed","Dear "&INDEX('Levers MC^2'!A:CL,MATCH("Guaranteed",'Levers MC^2'!CI:CI,0),68)&",",IF('Levers MC^2'!A2=HLOOKUP('Levers MC^2'!A2,'Levers MC^2'!A:CP,MATCH('Levers MC^2'!A2,'Levers MC^2'!A:A,0)+1,FALSE),"Dear "&INDEX('Levers MC^2'!A:CL,MATCH(,'Levers MC^2'!A:A,0)+AN14,68)&",",""))))

And I use part of the text in the results of the formula above to get a helper cell formula in AX40
=IFERROR(MID(A23,6,(FIND(",",A23)-1)-5),"")
which gives me the name used in the results of A23 so that I can find the name I need for AN40

The formula that I use to get the name in AN40/A44 is:
=IFERROR(INDEX('Levers MC^2'!A:CL,MATCH(AX40,'Levers MC^2'!BP:BP,0),64),"")

The name in AN32/A37 will always be the same.

I hope I have given you enough information to help me. I appreciate any assistance that you could provide to me! I have been working on this for 4 days now, and I have come up with nothing.<v:shapetype id=_x0000_t75 stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" o:preferrelative="t" o:spt="75" coordsize="21600,21600"> :confused:<v:stroke joinstyle="miter"></v:stroke><v:formulas><v:f eqn="if lineDrawn pixelLineWidth 0"></v:f><v:f eqn="sum @0 1 0"></v:f><v:f eqn="sum 0 0 @1"></v:f><v:f eqn="prod @2 1 2"></v:f><v:f eqn="prod @3 21600 pixelWidth"></v:f><v:f eqn="prod @3 21600 pixelHeight"></v:f><v:f eqn="sum @0 0 1"></v:f><v:f eqn="prod @6 1 2"></v:f><v:f eqn="prod @7 21600 pixelWidth"></v:f><v:f eqn="sum @8 21600 0"></v:f><v:f eqn="prod @7 21600 pixelHeight"></v:f><v:f eqn="sum @10 21600 0"></v:f></v:formulas><v:path o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"></v:path><o:lock aspectratio="t" v:ext="edit"></o:lock></v:shapetype><o:p></o:p>
<o:p></o:p>
~Rebecca<o:p></o:p>
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Sort by date Sort by votes
Welcome to the board.

I think you may find this task is more trouble than it's worth...

If the cell contains a formula, you CANNOT format just a portion of the result of that formala. Any formatting will apply to the entire result of the formula. Period, no way around it.

Your only option is to convert the formula and put the result of the formula in the cell, so the cell is just a value and no longer a formula.
Of coarse this means it becomes static, so you need to incorporate some Worksheet_Change Event code to recalculate the cells when values change.

THEN, on top of that, you have the not so easy task of finding which parts of the cell's value need to be formatted.


Yes, it can be done, but honestly your better off just applying a format to the entire cell to flag certain conditions.
Because all that code to do the above steps will definately hinder the performance of your sheet.
 
Upvote 0
Yeah, I am with you, however, this was a mark-up point on the document from my boss, and this is the way he wants it to show up.

I would be forever grateful if someone could help me devise a macro to meet this issue!

I would be happy to convert the text input in A37:AO42 and A44:AO55 directly to values, then just use the character position of the text along with a formula devised in the macro to insert the correct name with bold and orange within those cell's text results.
 
Last edited:
Upvote 0
Some specific details will be needed.

1. What are the formulas
2. What is the range holding those formulas
3. What are key strings that need to be highlighted
 
Upvote 0
Super Responder, jonmo1! Thank You...

So the text (STRING1), in full, that will be in the merged cells A37:O42 (RANGE1) is

My name is NAME1 , and I am your local Performance Manager.

NAME1 will always be the same, but I still want this in the macro for any time that it does change, and I cange the macro to the new name without hassling with CF.

The text (STRING2), in full, that will be in the merged cells A44:AK56 (RANGE2) is

My role is to assist your Media Manager, NAME2, in monitoring your monthly advertising results. My goal is to make sure your program is performing at a level that provides the greatest return on your investment with us. I will monitor your account closely to ensure that it performs well and drives quality leads to your business.

NAME2 changes depending on what NAME3 is in A23 (as there are different Account Managers assigned to each account).

(FORMULAS)

A23 contains the formula
=IF('Levers MC^2'!CI2="Tracked","Dear "&INDEX('Levers MC^2'!A:CL,MATCH("Tracked",'Levers MC^2'!CI:CI,0)+AN14,68)&",",IF('Levers MC^2'!CI2="Targeted","Dear "&INDEX('Levers MC^2'!A:CL,MATCH("Targeted",'Levers MC^2'!CI:CI,0),68)&",",IF('Levers MC^2'!CI2="Guaranteed","Dear "&INDEX('Levers MC^2'!A:CL,MATCH("Guaranteed",'Levers MC^2'!CI:CI,0),68)&",",IF('Levers MC^2'!A2=HLOOKUP('Levers MC^2'!A2,'Levers MC^2'!A:CP,MATCH('Levers MC^2'!A2,'Levers MC^2'!A:A,0)+1,FALSE),"Dear "&INDEX('Levers MC^2'!A:CL,MATCH('Levers MC^2'!A:A,0)+AN14,68)&",",""))))

Which produces the text result

Dear NAME3,

A23 does not need the formatting. My formula is fine here.

I use NAME3 in A23 to get a helper cell formula in AX40
=IFERROR(MID(A23,6,(FIND(",",A23)-1)-5),"")
which gives me just the name from A23 so that I can match it to the correct Account Manager I need displayed for NAME2 in the formula used for AN40.

The formula that I use in AN40 to get the NAME2 for A44:AK56 is:
=IFERROR(INDEX('Levers MC^2'!A:CL,MATCH(AX40,'Levers MC^2'!BP:BP,0),64),"")

If I have left anything out, please let me know, and I will answer you promptly.

Thanks,
Rebecca
 
Last edited:
Upvote 0
I posted the merged cells incorrectly in the original message. They are...

A37:O42 (RANGE1)
A44:AK56 (RANGE2)
 
Upvote 0
Sorry, but I can't make heads or tails out of anything you just wrote...


Here is a small example of a way to do this.

Imagine Cells A1:A10 have the formula =C1&D1

So if C1 = Barney and D1 = Rubble, then A1's formula results in BarneyRubble

So this code then monitors C1:D10 (because those are the cells effecting the formula in A1:A10).

And does the same thing the formula did, put's BarneyRubble in A1.
Anytime something changes in C1:D10, then the corresponding Cell in A1:A10 changes the value accordingly.

It then searches the value in Column A for a set of key strings (Stored in H1:H6).
And applies Bold Red Font to any of those strings appearing in A1:A10.


To use the code, Right click on the Sheet's tab, and select "View Code"
Then paste the following

Rich (BB code): 
Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyRange As Range, c As Range, x As Long, i As Long
 
'This monitors the Range C1:D10 for changes (those are cells effecting formula in column A.
Set MyRange = Intersect(Target, Range("C1:D10"))
If MyRange Is Nothing Then Exit Sub
 
For Each c In MyRange
    'Here is the part to replace the formula in the cells.
    'I've used a simple concatenate formula in column A =C1&D1
    
    Cells(c.Row, "A").Value = Cells(c.Row, "C") & Cells(c.Row, "D")
    
    'This clears font formatting
    Cells(c.Row, "A").Font.ColorIndex = 0
    Cells(c.Row, "A").Font.Bold = False
    
    'This searches the cell for each key value held in H1:H6
    For Each keystr In Range("H1:H6")
        x = InStr(Cells(c.Row, "A"), keystr)
        If x > 0 Then
        
            'If a Key value is found, this applies the format
            For i = x To x + Len(keystr) - 1
                Cells(c.Row, "A").Characters(i, 1).Font.ColorIndex = 3
                Cells(c.Row, "A").Characters(i, 1).Font.Bold = True
            Next i
        End If
    Next keystr
Next c
End Sub
 
Upvote 0
Thanks. I understand what you have here.

I guess maybe I did not say that I think option 2 would be my best option as it would automatically use the formula within the macro to format the text that it produces.

However, a situation that maybe you needed to know ahead of time is that for each time the formula in cell A23 changes NAME3, NAME2 will change.

What I need is a macro that will insert the new name within the string of text. The macro would need to have some type of formula built into it to dertermine which Account Manager's name to insert in the NAME2 space.

So for instance, if you are looking at the spreadsheet, and no formulas or macros were run, you would see... since all of the values would be flat text.

My name is , and I am your local Performance Manager.

My role is to assist your Media Manager, , in monitoring your monthly advertising results. My goal is to make sure your program is performing at a level that provides the greatest return on your investment with us. I will monitor your account closely to ensure that it performs well and drives quality leads to your business.


Once the A23 formula was input and the macro was run, the names would populate in NAME1 and NAME2, as the macro would look at the name produced in the helper cell AX40 once the formula is dropped in A23, and it would insert the propper NAME2 into the text.

Dear NAME3,

My name is NAME1, and I am your local Performance Manager.

My role is to assist your Media Manager, NAME2, in monitoring your monthly advertising results. My goal is to make sure your program is performing at a level that provides the greatest return on your investment with us. I will monitor your account closely to ensure that it performs well and drives quality leads to your business.

I appologize if you could not follow my thoughts before. I hope this helps to clarify what I am asking of you "Magic Macro Makers"!

Thanks,
Rebecca
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question
Find the row when a sum on a column with certain values on rows is >= of a value
Replies
7
Views
973
XOR LX
XOR LX
H
  • Question
Vba to change Excel file Properties like Title, Description, Fiscal, Period, file
Replies
0
Views
833
hemant_sonawane
H
S
  • Question
Correct Formulas for Mathematical Equations.
Replies
3
Views
757
siash69
S
M
  • Question
Counting between 2 worksheets where 1 is filtered.
Replies
0
Views
520
M4rek
M
R
  • Question
UserForm help
Replies
2
Views
718
rholdren
R

Forum statistics

Threads
1,215,219
Messages
6,123,692
Members
449,117
Latest member
Aaagu

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top