Formatting Script

[meppwc]

Has anyone know of a script or macro, or some way of applying formatting to an entire worksheet.
It would be great if I could:
- Set all cells to bold
- Set borders
- Change all letters to upper case
- Set the font and font size

 

[LazyBug]

What is the rough size of the table on this worksheet?

 

[meppwc]

10,000 rows and columns extend to BA

 

[Joe4]

For all except the upper case piece, you should be able to get the code you need for that very easily by turning on your Macro Recorder and recording yourself manually applying that formatting to the sheet. So you can get all that code that way.

For the Upper Case piece, assuming that you are talking about new data being entered, here is some code that will do that.
Just right-click on the sheet tab name at the bottom of your screen, select "View Code", and paste this VBA code in the VB Editor window that pops up:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range, cell As Range
    
    Set rng = Intersect(Target, Range("A1:BA10000"))
    
    If rng Is Nothing Then Exit Sub
    
    Application.EnableEvents = False
    For Each cell In rng
        cell.Value = UCase(cell.Value)
    Next cell
    Application.EnableEvents = True

End Sub

Now, as any data is manually entered in the range A1:BA10000, it will automatically be converted to upper case.

 

[meppwc]

Excellent. Although I should thought about the recorder part of the solution. The VBA part what the missing piece of the puzzle. Thanks so much Joe4

 

[Joe4]

You are welcome.
Glad we were able to help!

 

[meppwc]

You are welcome.
Glad we were able to help!

Hi Joe4...........I just noticed that if I insert a row, the code breaks and no longer works. Also, I noticed that the entire code disappears. Is there a way to avoid this from happening

 

[Joe4]

Hi Joe4...........I just noticed that if I insert a row, the code breaks and no longer works. Also, I noticed that the entire code disappears. Is there a way to avoid this from happening

There is no way that inserting a row would "make the code disappear". Inserting rows of data on the sheet cannot update VBA code!
If that is really happening, I think you may either be looking in the wrong place (there are different VBA modules within a single workbook), or you have some serious corruption issues going on!

To update the code so that it can handle inserting/deleting rows, make this update:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range, cell As Range
    
    If Target.CountLarge>1 Then Exit Sub

    Set rng = Intersect(Target, Range("A1:BA10000"))
    
    If rng Is Nothing Then Exit Sub
    
    Application.EnableEvents = False
    For Each cell In rng
        cell.Value = UCase(cell.Value)
    Next cell
    Application.EnableEvents = True

End Sub

Note that if your code no longer seems to be running automatically, manually run this VBA procedure:

Sub ReEnableEvents()
    Application.EnableEvents = True
End Sub

or simply close Excel and re-open it.

 

[meppwc]

Thanks again

 

[meppwc]

Hi again Joe4. I just discovered something. If I change a formula, I get the correct result displayed in the cell, but the formula goes away in the formula bar. The end result value exists there. I had to remove the VB procedure in order to once again see the formula in the formula bar.