Automatically bold certain phrases within a cell

[AFawcett]

Hi all

I am an Excel novice and have so far learnt to make the attached spreadsheet. It is for automatically calculating reagents I need in my chemistry experiments, so I have three worksheets: a 'database' of reagents, the calculator itself, and a summary page (which I will eventually format so I can print it).

In the 'Summary' worksheet I want to highlight certain phrases in bold automatically - and from a quick internet search I think I will need to use a macro. I have found some suggestions online but cannot get any to work.

In cell C24 I have some phrases, such as 'R1-Explosive when dry' and 'R2-Risk of explosion by shock, friction, fire or other source of ignition', and would like to know how to bold these phrases individually as and when the cell is populated with a list of phrases.

I would like certain phrases to be bold, and not others, as I would like to only highlight the more dangerous phrases.

Anyone have any ideas how to solve this problem?

Thanks

[stnkynts]

I would like certain phrases to be bold, and not others, as I would like to only highlight the more dangerous phrases.

All these parameters need to be identified. We only know what you tell us. The code is the same way.

how to bold these phrases individually as and when the cell is populated with a list of phrases.

What? Bold which cells. Just C24? Other cells in the summary sheet. Some clarification is needed.

[AFawcett]

All these parameters need to be identified. We only know what you tell us. The code is the same way.



What? Bold which cells. Just C24? Other cells in the summary sheet. Some clarification is needed.

To clarify the above:

For now it doesn't necessarily matter what phrases I want to be bolded or not, as I am able (hopefully) to take help given and adapt it myself to include the phrases I need bolded.

I will be generating lists of phrases as now shown in the updated spreadtsheet, such as "--R1-Explosive when dry--R2-Risk of explosion by shock, friction, fire or other source of ignition--R3-Extreme risk of explosion by shock, friction, fire or other source of ignition--R4-Forms very sensitive explosive metallic compounds--R5-Heating may cause an explosion--R6-Explosive with or without contact with air"

For example I only want "R1-Explosive when dry" and "R6-Explosive with or without contact with air" to be in bold, but the others just normal.

If you can show how to only bold the above phrases, within the string of many, I will be very grateful.

[protonLeah]

C24 has a very long concatenation formula, not a string value; you can't bold parts of it.

[AFawcett]

How can I achieve the same outcome (a list of phrases) whilst also being able to bold certain phrases?

[Leith Ross]

Hello AFawcett,

I added a formula, a column, and conditional formatting to the "R-Phrases" worksheet. When the value in B24 of the "Summary" worksheet changes, the phrases that have a "Y" in the row for that reagent will be made bold on the "R-Phrases" worksheet. The formula in C24 of the "Summary" worksheet has been discarded. The attached workbook has the changes already made.

Formula in Column "C1" of the worksheet "R-Phrases"
=INDEX(Reagents!$G$6:$BV$12,MATCH(Summary!$B$24,Reagents!$A$6:$A$12,0),ROW())

Conditional Formatting Rule in B1
Rule Type: User Formula to determine which cells to format
Formula: =OFFSET($B1,0,1,1,1)="Y"

[AFawcett]

Hi Leith Ross

I follow what you have done, but it's unfortunately not what I require. The formula in C24 is required as I need the generated list of phrases to be present on the Summary worksheet.

I have found a semi-workaround myself using the UPPER function, where each phrase I need highlighting in bold is now highlighted in capitals instead.

Is there a way to create a "BOLD" function that works identically to the UPPER function, but changes the text from normal to bold instead?

Thanks

[protonLeah]

One solution is to have the macro replace the formula results with the concatenated string values. The parts of the string can be individually formatted but you will loose the formula. Or, you can leave C24 as is and have a copy of the values elsewhere on the sheet

[AFawcett]

One solution is to have the macro replace the formula results with the concatenated string values. The parts of the string can be individually formatted but you will loose the formula. Or, you can leave C24 as is and have a copy of the values elsewhere on the sheet

I think I am following what you're saying...

Are you suggesting that I put my current formula in C24 elsewhere, for example in D24, then have C24 = D24. This will then generate a string of text, and not concatenated string value, that I can individually format using a macro?

Thanks

[protonLeah]

The macro below has to be edited to list the r-phrases to be bolded. I then clears summary C24. it finds the summary B24 value in the reagents sheet column A. It tests the r-phrases in that row for "Y" and builds a string appending the phrases to the end of the string. The final string is copied to summary C24 and the code searches the sting for the r-phrases specified and bolds that phrase.

Please Login or Register  to view this content.

[AFawcett]

Wow nice job. This is exactly what I was looking for. I had settled for a lesser option (bolding the whole string of text if a certain phrase appears), but will now update my spreadsheet to fit this in!

Thanks!