Macro for Formatting

N

Nadiya

Hello, Would anyone know how to do a search on Bold, uppercase text only and
increase it's font size? I have a huge spreadsheet that needs to be
formatting - it literally takes days. Thank you!
(oh - and maybe even throw in a page break right before the uppercase text)
 
B

Bernard Liengme

This is something to work on

Sub MakeBig()
Set rng = Range("A1:J20")
For Each mycell In rng
If Application.WorksheetFunction.IsText(mycell) And mycell.Font.Bold And
UCase(mycell) = mycell Then
mycell.Font.Size = 16
End If
Next
End Sub
 
D

Dave Peterson

I would do the pagebreaks separately--well, unless you gave more info.

This cycles through each cell looking for uppercase letters and checks to see if
they're bold.

Then changes the font size.

Option Explicit
Sub testme()
Dim myCell As Range
Dim myRng As Range
Dim wks As Worksheet
Dim myOldFontSize As Double
Dim myNewFontSize As Double
Dim lCtr As Long

myOldFontSize = 10
myNewFontSize = 16

Set wks = Worksheets("sheet1")

With wks
Set myRng = Nothing
On Error Resume Next
Set myRng = .UsedRange.Cells.SpecialCells _
(xlCellTypeConstants, xlTextValues)
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "no Text contants"
Else
For Each myCell In myRng.Cells
If InStr(1, myCell.Value, LCase(myCell.Value), _
vbBinaryCompare) <> 0 Then
'all non-upper case
'do nothing
Else
For lCtr = 1 To Len(myCell.Value)
If myCell.Characters(lCtr, 1).Text Like "[A-Z]" Then
If myCell.Characters(lCtr, 1).Font.Bold = True Then
If myCell.Characters(lCtr, 1).Font.Size _
= myOldFontSize Then
myCell.Characters(lCtr, 1).Font.Size _
= myNewFontSize
End If
End If
End If
Next lCtr
End If
Next myCell
End If
End With

End Sub
 
Top