Returns a Range object that represents all the cells on the worksheet (not just the cells that are currently in use).
expression A variable that represents a Worksheet object.
Because the default member of Range forwards calls with parameters to the Item property, you can specify the row and column index immediately after the Cells keyword instead of an explicit call to Item.
Using this property without an object qualifier returns a Range object that represents all the cells on the active worksheet.
This example sets the font size for cell C5 on Sheet1 of the active workbook to 14 points.
Worksheets("Sheet1").Cells(5, 3).Font.Size = 14
This example clears the formula in cell one on Sheet1 of the active workbook.
This example sets the font and font size for every cell on Sheet1 to 8-point Arial.
With Worksheets("Sheet1").Cells.Font .Name = "Arial" .Size = 8 End With
This example toggles a sort between ascending and descending order when you double-click any cell in the data range. The data is sorted based on the column of the cell that is double-clicked.
Option Explicit Public blnToggle As Boolean Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Dim LastColumn As Long, keyColumn As Long, LastRow As Long Dim SortRange As Range LastColumn = Cells.Find(What:="*", After:=Range("A1"), SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column keyColumn = Target.Column If keyColumn <= LastColumn Then Application.ScreenUpdating = False Cancel = True LastRow = Cells(Rows.count, keyColumn).End(xlUp).Row Set SortRange = Target.CurrentRegion blnToggle = Not blnToggle If blnToggle = True Then SortRange.Sort Key1:=Cells(2, keyColumn), Order1:=xlAscending, Header:=xlYes Else SortRange.Sort Key1:=Cells(2, keyColumn), Order1:=xlDescending, Header:=xlYes End If Set SortRange = Nothing Application.ScreenUpdating = True End If End Sub
This example looks through column C of the active sheet, and for every cell that has a comment, it puts the comment text into column D and deletes the comment from column C.
Public Sub SplitCommentsOnActiveSheet() 'Set up your variables Dim cmt As Comment Dim rowIndex As Integer 'Go through all the cells in Column C, and check to see if the cell has a comment. For rowIndex = 1 To WorksheetFunction.CountA(Columns(3)) Set cmt = Cells(rowIndex, 3).Comment If Not cmt Is Nothing Then 'If there is a comment, paste the comment text into column D and delete the original comment. Cells(rowIndex, 4) = Cells(rowIndex, 3).Comment.Text Cells(rowIndex, 3).Comment.Delete End If Next End Sub