formula that will change a selected word to a red font

ABabeNChrist
SilverLounger
Posts: 1868
Joined: 25 Jan 2010, 14:00
Location: Conroe, Texas

formula that will change a selected word to a red font

Post by ABabeNChrist »

Is there a formula that will change a selected word to a red font? But only that selected word.
Using the words “Safety Hazards” or “Dangerous”
Here is a sample how it may appear
Exposed wires are a Safety Hazard and correction needed to be made

User avatar
HansV
Administrator
Posts: 79030
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: formula that will change a selected word to a red font

Post by HansV »

No, you cannot format part of the result of a formula differently.
Best wishes,
Hans

ABabeNChrist
SilverLounger
Posts: 1868
Joined: 25 Jan 2010, 14:00
Location: Conroe, Texas

Re: formula that will change a selected word to a red font

Post by ABabeNChrist »

Thank You HansV
I first searched around couldn’t find anything and I'm sure that’s the reason why
No Can Do

User avatar
sdckapr
3StarLounger
Posts: 392
Joined: 25 Jan 2010, 12:21

Re: formula that will change a selected word to a red font

Post by sdckapr »

This is one of my pet peeves with XL. This is something one could easily do with Lotus123 R2.4 with WYSIWYG long before XL97 was even released, but something still not built in to XL...

Steve

steveh
SilverLounger
Posts: 1952
Joined: 26 Jan 2010, 12:46
Location: Nr. Heathrow Airport

Re: formula that will change a selected word to a red font

Post by steveh »

ABabeNChrist wrote:Is there a formula that will change a selected word to a red font? But only that selected word.
Using the words “Safety Hazards” or “Dangerous”
Here is a sample how it may appear
Exposed wires are a Safety Hazard and correction needed to be made
Hi There

I know that you have asked for a formula but I am also aware that your Workbook has several Macros as I have been following the posts. If a Macro solution is OK please see the attached.

This is not mine so if it is of use you can thank the author Mark Sloboda at this forum post http://www.ozgrid.com/forum/showthread.php?t=68172" onclick="window.open(this.href);return false;
You do not have the required permissions to view the files attached to this post.
Steve
http://www.freightpro-uk.com" onclick="window.open(this.href);return false;
“Tell me and I forget, teach me and I may remember, involve me and I learn.”
― Benjamin Franklin

User avatar
HansV
Administrator
Posts: 79030
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: formula that will change a selected word to a red font

Post by HansV »

Keep in mind that you would have to replace formulas with their values before running (a modified version of) the code from the workbook attached by SteveH. The code will not touch cells with formulas.
Best wishes,
Hans

ABabeNChrist
SilverLounger
Posts: 1868
Joined: 25 Jan 2010, 14:00
Location: Conroe, Texas

Re: formula that will change a selected word to a red font

Post by ABabeNChrist »

First off thank you Steve for macro
I have made some simple changes to the macro. I removed the bold and changed the font color to “3” (red).
And since I use the dreaded merged cells I had to change my endcolumn to the number of merged cells before it would work.
I decided not to attach to any existing code, but use within a module as designed. I also then attached to selected sheet “Summary”

Code: Select all

 Private Sub Worksheet_Activate()
  ColorandBold   ‘Name of Macro
 End Sub

Private Sub Worksheet_Deactivate()
  ColorandBold
 End Sub
That will run macro and make changes to sheet when opened
Is there a way I can have the macro run in the back ground

User avatar
HansV
Administrator
Posts: 79030
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: formula that will change a selected word to a red font

Post by HansV »

ABabeNChrist wrote:Is there a way I can have the macro run in the back ground
You could call it from the Worksheet_Change event (this runs whenever the user changes the contents of a cell) or from the Worksheet_Calculate event (this runs whenever a formula on the worksheet is recalculated). In the Worksheet_Change event, you can inspect the Target (the range of cells that have been modified), so that you can call your macro only if relevant. You can't perform such a check in the Worksheet_Calculate event (it doesn't have a Target argument), so you can't limit when it runs.
Best wishes,
Hans

ABabeNChrist
SilverLounger
Posts: 1868
Joined: 25 Jan 2010, 14:00
Location: Conroe, Texas

Re: formula that will change a selected word to a red font

Post by ABabeNChrist »

Hi HansV
I did as you mentioned and attached macro to Worksheet_Change
At first I couldnt get it to work, there was a BOO BOO somewhere, I then retyped the code and it worked great. :clapping:

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
  Call ColorandBold
End Sub
Once again Thanks HansV
And thank you Steve for this macro