How to: Programmatically Change Formatting in Worksheet Rows Containing Selected Cells

 

You can change the font of an entire row that contains a selected cell so that the text is bold.

Applies to: The information in this topic applies to document-level projects and VSTO add-in projects for Excel. For more information, see Features Available by Office Application and Project Type.

To make the current row bold and the previously bolded row normal

  1. Declare a static variable to keep track of the previously selected row.

            Static previousRow As Integer = 0
    

  2. Retrieve a reference to the current cell using the ActiveCell property.

            Dim currentCell As Excel.Range = Me.Application.ActiveCell
    

  3. Style the current row bold using the EntireRow property of the active cell.

            currentCell.EntireRow.Font.Bold = True
    

  4. Ensure that the current value of previousRow is not 0. A 0 (zero) indicates that this is the first time through this code.

            If previousRow <> 0 Then
    

  5. Ensure that the current row is different from the previous row.

                If currentCell.Row <> previousRow Then
    

  6. Retrieve a reference to a range that represents the row that was previously selected, and set that row to not be bold.

                    Dim rng As Excel.Range = DirectCast(ws.Rows(previousRow), Excel.Range)
                    rng.EntireRow.Font.Bold = False
    

  7. Store the current row so that it can become the previous row on the next pass.

            previousRow = currentCell.Row
    

The following example shows the complete method.

    Private Sub BoldCurrentRow(ByVal ws As Excel.Worksheet)

        ' Keep track of the previously bolded row.
        Static previousRow As Integer = 0

        ' Work with the current active cell.
        Dim currentCell As Excel.Range = Me.Application.ActiveCell

        ' Bold the current row.
        currentCell.EntireRow.Font.Bold = True

        ' If a pass has been done previously, make the old row not bold.
        ' Make sure previousRow is not 0 (otherwise this is your first pass through).
        If previousRow <> 0 Then

            ' Make sure the current row is not the same as the previous row.
            If currentCell.Row <> previousRow Then

                Dim rng As Excel.Range = DirectCast(ws.Rows(previousRow), Excel.Range)
                rng.EntireRow.Font.Bold = False
            End If
        End If

        ' Store the new row number for the next pass.
        previousRow = currentCell.Row
    End Sub

Working with Worksheets
How to: Programmatically Apply Styles to Ranges in Workbooks
How to: Programmatically Copy Data and Formatting across Worksheets
Optional Parameters in Office Solutions

Show: