VBA Course: Properties
We will now act on the content and appearance of cells and sheets.
Start by opening the editor, add a module, copy the macro below and associate it with a form button (refer to the selections page if needed):
Sub properties()
'Incomplete macro
Range("A8")
End Sub
We want to perform an action on cell A8 with this start of the macro.
To display the list of possibilities that can be associated with the Range object, add a . after Range("A8"):

The editor then displays the different possibilities...
For this first example, click on Value and then press the Tab key to validate this choice:
Sub properties()
'Incomplete macro
Range("A8").Value
End Sub
The Value property here represents the content of the cell.
We now want to give the value 48 to cell A8:
Sub properties()
'Cell A8 = 48
Range("A8").Value = 48
'Translation:
'The value of cell A8 is now: 48
End Sub
Then, the value Text example to A8 (the text must be put between " "):
Sub properties()
'Cell A8 = Text example
Range("A8").Value = "Text example"
End Sub
In this case, it is indeed cell A8 of the sheet where the procedure is launched (here, the one where the form button is located) that will be modified.
If you create a second button on sheet 2, it will be cell A8 of sheet 2 that will be modified.
To modify cell A8 of sheet 2 by clicking on the button of sheet 1, you must specify the name of the sheet by adding Sheets("Sheet_name") before Range:
Sub properties()
'Cell A8 of sheet 2 = Text example
Sheets("Sheet2").Range("A8").Value = "Text example"
End Sub
Similarly, if you want to modify cell A8 of sheet 2 of another open workbook, you must specify the name of the workbook at the beginning of the line using Workbooks("File_name"):
Sub properties()
'Cell A8 of sheet 2 of workbook 2 = Text example
Workbooks("Book2.xlsx").Sheets("Sheet2").Range("A8").Value = "Text example"
End Sub
Although Value was used to illustrate these different examples, it is not necessary to indicate it, as it is automatically the value of the cell that is modified if nothing is specified.
These 2 lines generate an identical result:
Range("A8").Value = 48
Range("A8") = 48
Text Formatting
After selecting the Font property and adding a ., the list of properties that can be assigned to text formatting appears:

Color modification will be detailed on the next page...
Formatting: text size
Sub properties()
'Change the text size of cells A1 to A8
Range("A1:A8").Font.Size = 18
End Sub
Formatting: bold text
Sub properties()
'Bold the cells A1 to A8
Range("A1:A8").Font.Bold = True
End Sub
Bold = True means Bold characters = Yes.
To remove the Bold formatting from a text, replace Yes with No, in other words, True with False:
Sub properties()
'Remove "Bold" formatting from cells A1 to A8
Range("A1:A8").Font.Bold = False
End Sub
Formatting: italic text
Sub properties()
'Italicize cells A1 to A8
Range("A1:A8").Font.Italic = True
End Sub
Formatting: underlined text
Sub properties()
'Underline cells A1 to A8
Range("A1:A8").Font.Underline = True
End Sub
Formatting: font
Sub properties()
'Change the font of cells A1 to A8
Range("A1:A8").Font.Name = "Arial"
End Sub
Add borders

Sub properties()
'Add a border to cells A1 to A8
Range("A1:A8").Borders.Value = 1
'Value = 0: no border
End Sub
Modify the formatting of the current selection
Sub properties()
'Add a border to selected cells
Selection.Borders.Value = 1
End Sub
Modify the properties of a sheet
Sub properties()
'Hide a sheet
Sheets("Sheet3").Visible = 2
'Visible = -1: display the sheet
End Sub
Modify the value of a cell based on another

The goal here is for A7 to take the value of A1, which gives us:
Sub properties()
'A7 = A1
Range("A7") = Range("A1")
'Or:
'Range("A7").Value = Range("A1").Value
End Sub
Or to copy the text size, for example:
Sub properties()
Range("A7").Font.Size = Range("A1").Font.Size
End Sub
Modify the value of a cell based on its own value
We will now create a click counter here.
With each click, the value of A1 will be increased by 1:
Sub properties()
'Click counter in A1
Range("A1") = Range("A1") + 1
End Sub
This line should not be interpreted as a mathematical operation (remember that what is on the left of the = takes the value of what is on the right of the =).
Excel executes the code line by line respecting certain priorities, these comments should help you understand this code better:
'For this example: A1 is worth 10 before the code execution
Sub properties()
'A click has been made on the button, we enter the procedure
'For the moment A1 is still worth 10
'During the execution of the line below:
'- the value to the right of the = is calculated first (A1 is still worth 10, this gives 10 + 1)
'- after calculation, the value to the right of the = is therefore 11
'- A1 then takes the value to the right of the = (or the value 11)
Range("A1") = Range("A1") + 1
'A1 is then worth 11 only after the execution of the line of code
End Sub
With
This code allows you to define different properties for cell A8 of sheet 2:
Sub properties()
Sheets("Sheet2").Range("A8").Borders.Weight = 3
Sheets("Sheet2").Range("A8").Font.Bold = True
Sheets("Sheet2").Range("A8").Font.Size = 18
Sheets("Sheet2").Range("A8").Font.Italic = True
Sheets("Sheet2").Range("A8").Font.Name = "Arial"
End Sub
We can use With to avoid repeating Sheets("Sheet2").Range("A8"):
Sub properties()
'Start of the instruction with: With
With Sheets("Sheet2").Range("A8")
.Borders.Weight = 3
.Font.Bold = True
.Font.Size = 18
.Font.Italic = True
.Font.Name = "Arial"
'End of the instruction with: End With
End With
End Sub
Sheets("Sheet2").Range("A8") is therefore no longer repeated.
Although it is not essential in this case, it is also possible to do the same for .Font, which would give us:
Sub properties()
With Sheets("Sheet2").Range("A8")
.Borders.Weight = 3
With .Font
.Bold = True
.Size = 18
.Italic = True
.Name = "Arial"
End With
End With
End Sub