How To Use VBA To Change Excel Cell Style Or Text Font, Color, Size Programmatically

This article will show you how to use VBA to programmatically set the Excel cell style, text’s font, color, and size with an example.

1. How To Get Excel Cell Objects In VBA.

1. Use the Range() function to get the Excel cell Range object in VBA.

   Range("A1:B6")

2. Use the Selection object to get all the selected Excel cells of the current worksheet in VBA.

2. How To Change Excel Cell Style In VBA.

1. Use the above Range or Selection object’s Style property to change the Excel cell style.

   Sub ChangeCellStyle()
        
        Selection.Style = "Good"
        
        Range("A1:B2").Style = "Normal"
        
   End Sub
   

2. When you run the above VBA script, you will get the below Excel cell style.
   
3. You can click Home —> Cell Styles drop-down list in the Styles group to get all the cell style names.

3. How To Change Text Font Name, Color, and Size Use VBA In Excel Cell.

1. You can use the Range or Selection object’s Font property to change the Excel cell text’s font, color, and size.
2. Use the Font.Name property to set the cell text’s font name.

   Selection.Font.Name = "Book Antiqua"

3. Use the Font.Color property to set the cell text’s color value.

   'Selection.Font.Color = RGB(0, 255, 0) 
   Selection.Font.Color = vbBlue

4. The color value can be created with the RGB(red, green, blue) function or use the limited built-in vbColors values.
5. The vbColors value includes vbBlack, vbBlue, vbCyan, vbGreen, vbMagenta, vbRed, vbWhite, vbYellow.
6. Use the Font.Size property to set the Excel cell text’s size value.

   Selection.Font.Size = 100

4. How To Use VBA To Change Excel Cell Style Or Text Font, Color, Size Programmatically Example.

1. In this example, there are 3 worksheets in the Excel workbook.
2. When you run the VBA script, it will change the Excel cell style and font name in the first worksheet.
3. Change the font text color in the second worksheet.
4. And change the font text size in the third worksheet.
5. Below is the example source code, you can see the code comments for a detailed explanation.

   'This function will change worksheet cell text's font, color, and size differently in different worksheets.
   
   Sub changeFontEntireWorkbook()
     
       ' Define a variable to record the worksheet number.
       Dim sheetNumber As Integer
       
       sheetNumber = 1
       
       ' Loop each worksheet in the workbook.
       For Each Worksheet In Worksheets
             
           ' Activate the current worksheet.
           Worksheet.Activate
           
           ' Select all the used excel range cells.
           ActiveSheet.UsedRange.Select
           
           ' Change the font name & cell style in the first worksheet.
           If sheetNumber = 1 Then
           
               Selection.Font.Name = "Book Antiqua"
               
               Selection.Style = "Good"
               
           ' Change the font color in the second worksheet.
           ElseIf sheetNumber = 2 Then
               
               'Selection.Font.Color = RGB(0, 255, 0)
               Selection.Font.Color = vbBlue
               
           ' Change the font size in the third worksheet.
           ElseIf sheetNumber = 3 Then
              
               Selection.Font.Size = 100
               
           End If
           
           
           ' worksheet number plus 1.
           sheetNumber = sheetNumber + 1
               
       
       Next
   
   End Sub