I have a VB code, which I want to make some text bold and some not..

Marq

Well-known Member
Joined
Dec 13, 2004
Messages
914
Office Version
  1. 365
  2. 2007
Platform
  1. Windows
below is a vb code, which basically populates headers (left and center) with project information.

I fill in the cells in column "B" on the header sheet, then click "Update Headers" button and the info in column B is fed to the headers of two other pages in the workbook.

Some of the header items are not bold and i want them to be bold and other items are bold and i want them to be normal.

ALSO....two items I want to be RED and BOLD: Here is the code...after the code I will list the items I want to edit:

VBA Code:
Sub WrkshtHeader()
Dim Headers As Worksheet
Dim Master As Worksheet
Dim CoatingsProgress As Worksheet
Dim InsulationProgress As Worksheet

Dim JobDesc As Range
Dim WO As Range
Dim PO As Range
Dim EstAsLead As Range
Dim Asbestos As Range
Dim SurfacePrepOPS As Range
Dim SurfacePrepPO As Range
Dim EnclosePCOPS As Range
Dim EnclosePCPO As Range
Dim RemoveInsINSOPS As Range
Dim RemoveInsINSPO As Range
Dim CleanUPOPS As Range
Dim CleanUPPO As Range
Dim FCOOPS As Range
Dim FCOPO As Range
Dim LeadResults As Range

Set Headers = Worksheets("Headers")
Set Master = Worksheets("Master")
Set Ins = Worksheets("Insulation Progress")
Set Coat = Worksheets("Coatings Progress")
   
Set JobDesc = Headers.Range("B2")
Set WO = Headers.Range("B3")
Set PO = Headers.Range("B4")
Set EstAsLead = Headers.Range("B5")
Set LeadResults = Headers.Range("B6")
Set Asbestos = Headers.Range("B7")
Set SurfacePrepOPS = Headers.Range("B8")
Set EnclosePCOPS = Headers.Range("B9")
Set RemoveInsINSOPS = Headers.Range("B10")
Set CleanUPOPS = Headers.Range("B11")
Set FCOOPS = Headers.Range("B12")


  
    On Error GoTo errExit
    Application.ScreenUpdating = False
    Application.EnableEvents = False
   
Ins.Activate
    With Ins.PageSetup
        .LeftHeader = "&""Arial""&16OPS-PO" & Chr(10) & "&14Enclose/PC: " & EnclosePCOPS & Chr(10) & "&14Remove/Install INS: " & RemoveInsINSOPS & Chr(10) & "&14CleanUp: " & CleanUPOPS & Chr(10) & "&14FCO: " & FCOOPS & Chr(10)
        .CenterHeader = "&""Arial,Bold""&24" & JobDesc & Chr(10) & "&16Insulation Progress" & Chr(10) & "&14ASBESTOS? " & Asbestos & Chr(10) & "&14Work Order # " & WO & Chr(10) & "&14PO# " & PO
    End With
   
Coat.Activate
    With Coat.PageSetup
        .LeftHeader = "&""Arial""&16OPS-PO" & Chr(10) & "&14SP/Paint/Watchers: " & SurfacePrepOPS & Chr(10) & "&14CleanUp: " & CleanUPOPS & Chr(10) & "&14FCO: " & FCOOPS & Chr(10) & "&14Estimated as LEAD? " & EstAsLead & Chr(10) & "&14LEAD Results: " & LeadResults & Chr(10)
        .CenterHeader = "&""Arial,Bold""&24" & JobDesc & Chr(10) & "&16Coatings Progress" & Chr(10) & "&14WO# " & WO & Chr(10) & "&14PO# " & PO
    End With
       
Master.Activate
    With Master.PageSetup
        .CenterHeader = "&""Arial,Bold""&24" & JobDesc & Chr(10)
    End With

   

   
errExit:

Headers.Activate

MsgBox "Headers Update Completed!"

Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub

On Coat.Activate I would appreciate the following:

LEFT HEADER ITEMS TO EDIT:
the OPS-PO to be bold
The "Estimated As Lead?" value to be BOLD GREEN if it is NO and BOLD RED if it is YES
The LEAD Results vale to be BOLD GREEN if NEG and BOLD RED IF POS

CENTER HEADER ITEMS TO EDIT:
WO# and value i plug in header sheet to not be bold
PO# and value i plug in header sheet to not be bold

On Ins.Activate I would appreciate the following:
the OPS-PO to be bold
the ASBESTOS? result to be BOLD RED for YES and BOLD GREEN for NO
WO# and value i plug in header sheet to not be bold
PO# and value i plug in header sheet to not be bold

I would greatly appreciate anyone that can help me edit the code.

I've attached a pic of the header sheet where i plug all the info in column B.
 

Attachments

  • headers capture.JPG
    headers capture.JPG
    78.3 KB · Views: 10

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
mark, you say:
Some of the header items are not bold and i want them to be bold and other items are bold and i want them to be normal.
and yet your code is making the header BOLD by running literal-assignment code (e.g. - literal statements), block by block, for each of 3 different worksheets after activating them. If that's route you want to adopt, why don't you just write the rest of your code the same way and assign the specification of "bold" or "not bold" by way of a method where you "write the code block for the worksheets I want bolded" and "do not write the code block for the worksheets I don't want bolded"??
 
Upvote 0
mark, you say:and yet your code is making the header BOLD by running literal-assignment code (e.g. - literal statements), block by block, for each of 3 different worksheets after activating them. If that's route you want to adopt, why don't you just write the rest of your code the same way and assign the specification of "bold" or "not bold" by way of a method where you "write the code block for the worksheets I want bolded" and "do not write the code block for the worksheets I don't want bolded"??

the code I used was from a code that was written for me about 10 years ago. I copied and pasted it into this work book and plagerized it. The original code does not look like that. I had to figure out which line did what and once i cleared that hurdle I was able to make my headers populate with the text I wanted per worksheet.....i just couldnt figure out how to bold and unbold and color specific items in the code. I can leave it as it is..it all still works exactly how I want it to..i was just looking to make some eye candy with it.
 
Upvote 0
well that's what I would do. You can *probably* simplify it, but why go through such an effort if it works fine as is?
 
Upvote 0
well that's what I would do. You can *probably* simplify it, but why go through such an effort if it works fine as is?
i can live with it..i was just trying to "trick" it out a little..I just dont know how to make specific items bold or non bold...the bold you see in the code was a carry over from the original code...i left it in, but for the center for example, it makes everything bold..i dont want everything bold. I guess i can go in and edit the actual header after the fact but it would be cool to have it automated.

to make the green and red bold id assume it would require a formula of sum sort within the code itself and thats WAAAAYYYY beyond my knowledge.
 
Upvote 0
I think I've offered all I have here for you sir but I wish you luck!
 
Upvote 0
attached is what it looks like now (nobold.jpg) and how id like it look (edit.jpg).

but thanks anyway for reading my post. Its all confusing to me and im surprised i was able to edit the code to get me this far.
 

Attachments

  • edit.JPG
    edit.JPG
    22 KB · Views: 5
  • nobold.JPG
    nobold.JPG
    23.4 KB · Views: 5
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,875
Members
452,363
Latest member
merico17

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