MS Excel VBA to find range formatted with "Center across Selection" horizontal alignment

0
votes

I have an Excel spreadsheet with the text "Hello, this is a long line of text" in cell A1. I have selected cells A1:G1.

I know if I use the line Selection.HorizontalAlignment = xlCenterAcrossSelection in a macro, it will format the selected cells using the horizontal text alignment option "Center across Selection".

How can I write a function that will do the inverse operation, i.e. given cell A1, return the range A1:G1 ?

1
exceltext-alignmentvba
It's not clear from your question what are the criteria for returning A1:G1 given A1 ? A contiguous block of cells with "center" formatting? No gaps and only one column wide? – Tim Williams

1 Answers

0
votes

Here is a start:

Public Function CAS(r As Range) As String
    Dim i As Long, rng As Range
    CAS = ""

    If r.HorizontalAlignment H<> 7 Then Exit Function
    Set rng = r

    For i = 1 To Columns.Count
        If r.Offset(0, i).HorizontalAlignment <> 7 Then
            CAS = rng.Address(0, 0)
            Exit Function
        Else
            Set rng = Union(rng, r.Offset(0, i))
        End If
    Next i
End Function

This assumes that 7 is the correct enuneration for HorizontalAlignment