CODEWITHSUNDEEP
calcopenoffice-basic
Gurkenhobel
Gurkenhobel

Reputation: 59

How can I colorize all merged cells with a sheet of Open Office-Calc?

The title says it all:

I have a spreadshet in Open Office. How can I find out which cells are merged (horizontal and vertical) within the active sheet to colorize them. Horizontally using another color than vertical. Solutions are much appreciated, thanks in advance.

Upvotes: -1

Views: 42

Answers (1)

Jim K
Jim K

Reputation: 13790

@JohnSUN provided the following macro that searches for merged cells by using getIsMerged(), which you can modify to add color.

REM Author JohnSUN - [email protected]
Sub getMergedCells  ' Create an index of merged cells '
Const INDEX_NAME = "Index Of Merged Cells"  ' Name of sheet for a table of contents and header '
Dim oSheets As Variant  ' All sheets of current spreadsheet '
Dim oSheet As Variant   ' Single sheet '
Dim i As Long           
Dim nCount As Long      ' Count of merged cells '
Dim oCellFormatRanges As Variant
Dim oEnum As Variant
Dim oCell As Variant    ' Single cell '
Dim oCursor As Variant  ' Range of mergd cells '
Dim sRes As String      
Dim aRes As Variant     ' Array of all merged cells '
    oSheets = ThisComponent.getSheets()
    aRes = Array(Array(INDEX_NAME))
    For i = 0 To oSheets.getCount()-1   ' For each sheet in current workbook '
        oSheet = oSheets.getByIndex(i)
        oCellFormatRanges = oSheet.getCellFormatRanges()
        oEnum = oCellFormatRanges.createEnumeration()
        Do While oEnum.hasMoreElements()
            oCell = oEnum.nextElement()
            If oCell.getIsMerged() Then
                nCount = UBound(aRes)+1
                ReDim Preserve aRes(nCount)
                oCursor = oSheet.createCursorByRange(oCell)
                oCursor.collapseToMergedArea()
                sRes = "=HYPERLINK(""#"+oCell.AbsoluteName+""";"""+Join(Split(oCursor.AbsoluteName,"$"),"")+""")"
                aRes(nCount) = Array(sRes)
            EndIf
        Loop
    Next i
    If LBound(aRes()) > UBound(aRes()) Then
        MsgBox("No merged cells...")    
    Else
        If oSheets.hasByName(INDEX_NAME) Then oSheets.removeByName(INDEX_NAME)
        oSheets.insertNewByName(INDEX_NAME, 0)
        oSheet = oSheets.getByIndex(0)
        oSheet.getCellRangeByPosition(0, 0, 0, nCount).setFormulaArray(aRes)
        ThisComponent.getCurrentController().select(oSheet.getCellByPosition(0,1))  ' Show new index '
        ThisComponent.getCurrentController().select(ThisComponent.createInstance("com.sun.star.sheet.SheetCellRanges"))
    EndIf
End Sub

Source: https://ask.libreoffice.org/t/how-to-find-merged-cells-in-calc/19109/3

Upvotes: 0

Related Questions