title: excel vba: get cell before selection
body: <p>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:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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?</p>
  
  <p>best, US</p>