Ask Your Question
0

Conditional format cell borders based on adjacent cells [closed]

asked 2015-03-04 05:26:24 +0100

jarfil gravatar image

updated 2020-08-16 21:50:30 +0100

Alex Kemp gravatar image

In LibreOffice Calc (4.4.0.3, Windows), I can use Conditional Formatting to set the background of cells depending on their content:

Conditional formatting background of cells

However, I can't seem to find a way to conditionally set borders based on adjacent cell values being equal or not to each cell's content.

What I would like is to get a result like the following, without having to set the borders by hand:

Desired conditional formatting result

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-08-16 21:51:12.835403

Comments

This is certainly possible, but time consuming. You would have to create 16 styles for each of the possible values. Conditional formatting in this case it seems to me inappropriate. I think that simple macro that went through to all the cells in the selected range and painted them in the right color will be easier to implement.

JohnSUN gravatar imageJohnSUN ( 2015-03-04 11:15:40 +0100 )edit

1 Answer

Sort by » oldest newest most voted
0

answered 2015-03-04 12:18:36 +0100

JohnSUN gravatar image

updated 2015-03-04 16:02:03 +0100

You can use this simple code additionally to your conditional formatting

Sub paintBrdrs
Dim oSheet As Variant
Dim oStatInd As Variant
Dim oCursor As Variant
Dim aData As Variant
Dim oCell As Variant
Dim i&, j&
    oSheet = ThisComponent.getCurrentController().getActiveSheet()
    oCursor = oSheet.createCursor()
    oCursor.gotoStart()
    oCursor.gotoEndOfUsedArea(True)
    aData = oCursor.getDataArray()
    oStatInd = ThisComponent.getCurrentController().getFrame().createStatusIndicator()
    oStatInd.start("Please wait...", (UBound(aData)+1))
    For i = LBound(aData) To UBound(aData)
        oStatInd.setValue(i)
        For j = LBound(aData(0)) To UBound(aData(0))
            oCell = oSheet.getCellByPosition(j, i)
            'oCell.TopBorder = newBorder(aData, i, j, i-1, j) ' there is no need
            oCell.RightBorder = newBorder(aData, i, j, i, j+1)
            oCell.BottomBorder = newBorder(aData, i, j, i+1, j)
            'oCell.LeftBorder = newBorder(aData, i, j, i, j-1) ' there is no need
        Next j
    Next i
    oStatInd.end()
End Sub

Function newBorder(oData As Variant, currentY, currentX, testY, testX) As com.sun.star.table.BorderLine
Dim aBorder As New com.sun.star.table.BorderLine
  aBorder.OuterLineWidth = 0
  If (testY >= 0) And (testX >= 0) And (testY <= UBound(oData)) And (testX <= UBound(oData(0))) Then
    If (oData(currentY)(currentX)<>oData(testY)(testX)) Then aBorder.OuterLineWidth = 26
  EndIf
  newBorder = aBorder
End Function
edit flag offensive delete link more

Question Tools

2 followers

Stats

Asked: 2015-03-04 05:26:24 +0100

Seen: 535 times

Last updated: Mar 04 '15