We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

Conditional formatting-reset-button with Python macro in Calc?

asked 2020-08-09 13:08:15 +0200

Prevela gravatar image

updated 2021-05-22 18:33:31 +0200

Alex Kemp gravatar image
  • I have specific cell ranges that should always have specific conditional formatting
  • I'm going to move some of these cells around, delete their contents, cut them, etc.
  • Right now, I manually reset the ranges in Format -> Conditional -> Manage, deleting the single-celled ones, and reentering e.g. "A1:B1000" into one, and "C2:C1000" into another
  • Pressing a button that did this would be significantly better, as
  • Freezing/locking conditional formatting to specific cell ranges seems impossible for now
  • Python seems like the best language to use for macros
  • The Python-stuff I found is overwhelming, and speaks of installing an IDE, even though Calc seems to already have a built in editor

How do I go about doing this? =)

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2020-08-10 05:39:07 +0200

Yes, Python is great. A simple example:

import uno
from com.sun.star.beans import PropertyValue
from com.sun.star.sheet.ConditionOperator import EQUAL

def main():
    doc = XSCRIPTCONTEXT.getDocument()
    rango = doc.Sheets[0]['A1:A10']

    cf = rango.ConditionalFormat
    args = dict(
        Operator = EQUAL,
        Formula1 = '10',
        StyleName = 'Good',
    pv = [PropertyValue(Name=n, Value=v) for n, v in args.items()]
    rango.ConditionalFormat = cf

IMPORTANT: Property Formula1 always is String, StyleName should be exists.

edit flag offensive delete link more

answered 2020-08-10 07:13:51 +0200

newbie-02 gravatar image

besides learning python is no fail at all ... you need not re-invent the wheel ...

what about 'clear direct formatting'?

and depending on the style to work - fingers on mouse or on keyboard - a keyboard shortcut is often faster than a button, as you save the time to select the button as target,

and - oh wonder - 'clear direct formatting' already has one, thus you can save all that stuff with macro, programming, testing, key or shortcut assignment, transfer to other systems ...

just press ctrl-m,

and - one more wonder - for those who like buttons there already is one, its in the toolbar above the paint bucket for the background, right to the paintbrush for 'clone formatting', and looks like a letter 'A' with a red eraser,

isn't that 'good service'?

edit flag offensive delete link more


No. This is not good service. It is badly written, and doesn't answer the question; "Clear Direct Formatting" removes the formatting from the selected cells. It does not reset the formatting to what it was before

Prevela gravatar imagePrevela ( 2020-08-10 11:50:00 +0200 )edit

sorry, didn't check that you wanted 'reset' to a former - not mentioned - format ... for that ... macro ...

newbie-02 gravatar imagenewbie-02 ( 2020-08-10 17:29:31 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2020-08-09 13:08:15 +0200

Seen: 125 times

Last updated: Aug 10 '20