excel vba: get cell before selection

Question

I have the following problem in an Excel table. I want to have the same formatting in a line depending on the first cell. Here is what I came up with so far:

Sub LineFormatSynch()

  FSize = Selection.Offset(0, -1).Font.Size
  FName = Selection.Offset(0, -1).Font.Name
  FColor = Selection.Offset(0, -1).Font.Color
  FHAlign = Selection.Offset(0, -1).HorizontalAlignment
  FVAlign = Selection.Offset(0, -1).VerticalAlignment

  For Each c In Range("E196:BR196")

    c.Font.Size = FSize
    c.Font.Name = FName
    c.Font.Color = FColor
    c.HorizontalAlignment = FHAlign
    c.VerticalAlignment = FVAlign

  Next

End Sub

But it is not flexible enough. Basically I want to select a line and click execute macro and the selected cells should be formatted in the manner of the first cell. But I cannot figure out how to extract the address for the first cell from my selection. I have the row number in my selection.address like "$E$197:$BR$197". The cell I want the formatting from is always in column "D". How can I extract "197" from my selection.address? With a regular expression or is there a better way?

best, US

Answer 1

I found a simpler solution I think but thx. Here it is:

Sub LineFormatSynch()

  Dim RowNumber As Integer

  RowNumber = Selection.Row
  OriginAddress = "D" & CStr(RowNumber)

  FSize = Range(OriginAddress).Font.Size
  FName = Range(OriginAddress).Font.Name
  FColor = Range(OriginAddress).Font.Color
  FHAlign = Range(OriginAddress).HorizontalAlignment
  FVAlign = Range(OriginAddress).VerticalAlignment

  For Each c In Selection

    c.Font.Size = FSize
    c.Font.Name = FName
    c.Font.Color = FColor
    c.HorizontalAlignment = FHAlign
    c.VerticalAlignment = FVAlign

  Next

End Sub

Answer 2

Here's an example of a way to reference a table

Sub LineFormatSynch()
    Dim cl As Range
    Dim lst As ListObject
    Dim rw As Range

    Set cl = ActiveCell
    Set lst = cl.ListObject
    If Not lst Is Nothing Then ' in case ActiveCell is not in a table
        Set rw = lst.DataBodyRange.Rows(cl.Row - lst.DataBodyRange.Row + 1)

        With rw
            .Font.Size = cl.Font.Size
            .Font.Name = cl.Font.Name
            .Font.Color = cl.Font.Color
            .HorizontalAlignment = cl.HorizontalAlignment
            .VerticalAlignment = cl.VerticalAlignment
        End With
    End If
End Sub

Edit:

To use, select the cell you want to use as the template format for a row, then run the macro. It will:

1. Detect if the active cell is in a table.
2. Set a reference to the row the selected cell is in
3. Apply the format of the selcted cell to all cells in the same row in the table

Set lst = cl.ListObject provides a reference to the Table
If cl is not in a table, lst will be set to Nothing
lst.DataBodyRange provides a reference to the table data (excludes the header row)
Set rw = ... sets a reference to the table row containing the active cell
The five lines inside the With clause copy the properties from cl to all cells in rw

BTW this is tested on Excel 2010, if your version is different there may be some differences, let me know

Edit 2:

For a generic range of a sheet that just looks like a table, consider:

Sub LineFormatSynch()
    Dim cl As Range
    Dim lst As Range
    Dim rw As Range

    Set cl = ActiveCell
    Set lst = cl.CurrentRegion
    If Not lst Is Nothing Then
        Set rw = lst.Rows(cl.Row - lst.Row + 1)

        With rw
            .Font.Size = cl.Font.Size
            .Font.Name = cl.Font.Name
            .Font.Color = cl.Font.Color

            .HorizontalAlignment = cl.HorizontalAlignment
            .VerticalAlignment = cl.VerticalAlignment
        End With
    End If
End Sub

The current region is a range bounded by any combination of blank rows and blank columns