VBA / Macro to change font size based on another cell

I am writing a document in excel (it has to be in excel as there are some calculations required hence it cant be in Word) & require a VBA to change the font size based on another cell reference:

if i have a main-heading in A1 i would like it to be Calibri, size 30, in Bold based on B1 having the number "1" in it
if i have a sub-heading in A2 i would like it to be Calibri, size 25, in Bold based on B2 having the number "2" in it
if i have a sub-sub-heading in A3 i would like it to be Calibri, size 20, (Not in Bold) based on B3 having the number "3" in it.

having an identifiable trigger in column B seems to be the the most practical way of identifying the requirements for column A

i would like it to be simple so that i can add to it with other variables etc...

if anyone can help it would be really appreciated.

Could the different heading types be in any row in column A or only in 1, 2 or 3?

Are you wanting the formatting to change when you add a value to column B, or to trigger the code at some point and apply the formatting to all rows with a value in column B?

BSB

Assuming the headers could be in any row, and you want it to change when you add a value to B, try the below change event.

Please Login or Register  to view this content.

BSB

Hi i have added an excel file that shows what i want to achieve on the 2nd tab?

Hi Badlyspelledbouy, thanks for your Fix, i tried to insert it / paste it & couldn't get it to work, i have attached an Excel document that may help?

Here's your file with the code I provided earlier included. Add your 1s, 2s and 3s to column B and see if it works as you expect.

BSB

this is perfect thanks BSB

Glad I could help

BSB

Hi BSB, due to the size & complexity of my work book, i may have over-complicated things!!
is there anyway that the "Trigger" could be a symbol in the word in the cell i.e.

The quick brown fox jumps over the lazy dog - is normal
*The quick brown fox jumps over the lazy dog - (the trigger is the *) is Calibri 30 bold
"The quick brown fox jumps over the lazy dog" - (the trigger is the " ") is Calibri 25 bold

and this would apply to all cells?

Not sure I understand the requirement on the last (25 font size) example as not sure where the " " sits in the text string.

You'll have issues using the * symbol as it's a wildcard.

Not saying there's not a way to make it work for you, but am saying I'd need more information first.

Sorry!!

BSB

Hi & thanks again for reply.
i have re-attached the spreadsheet, i have to reference the cells with your solution, however the font adjustments dont carry over to column "D".
As such i wondered if there was a way that any word, in any cell, that has the word "The" in the title (or any other word), could be Calibri, 30, Bold?
that way it would change all instances?

Try this, but thoroughly try it before you roll it out to full use...

Please Login or Register  to view this content.

Change the X/Y/Z in the first three Cases to a suitable charatcter. But beware of wildcards causing problems.

Hope it's of help!

BSB

We cross posted at the same time.

I'll have a look at your latest upload and make changes where possible/necessary.

BSB

Nope, it makes less sense to me now... Column D is referenceing column A via a formula and therefore will only show the content and not the formatting.

Need more info on what you're actually trying to achieve. Late in the evening to be making guesses...

BSB

I appreciate your help with this & have attached the actual document i am trying to work on & will attempt to explain the way it works & how i would like it to look!!
The attached file is a scoping document, i have all the text in column "B" & if i put a character in column "C" then that specific text moved over to column "F", then spaces are removed & it is collated in column "i", once all the information is in Column "I" then i press the Paste button (at the top of column "I") and it copies it to the next tab "SCOPE" & this is where i need the formatting, i.e. the headers & sub headers in bold to be larger font etc..
I thought if i used your formula & put an "X" as the trigger in column "C" which increases the font, then the formatting would carry over but it doesn't!!
I then thought that if the trigger is a character in a word like an "X", "Y", "Z" like your second formula that they would all be picked up & the font increase, but this becomes doesn't work due to all the other formulas & the pasting between tabs!
Maybe the solution is a Macro on the "SCOPE" tab that can run after it has been pasted, that can look for the Trigger/identifier (either a different reference in an adjacent column, like an "X", or a trigger letter, or word in the heading, i.e. trigger is a number (or letter) & every heading has "1-ELECTRICAL SERVICES" & the font changes?
I hope this describes my problem & you have the patience & wisdom to assist?