Filling/removing BG color of a cell range using VBA

[Creqz]

Hello. Can someone please provide me a complete working example for doing the following two tasks:

1. Coloring the cell range A1:C3 with color #c2c2c2
2. Removing the color (like what would be the case if one selected “No Fill” from the background color picker) from cell range A1:C3

I’ve tried numerous examples from websites and forums and nothing has worked; I’m guessing that there are standard declarations one must use in VBA that I simply don’t know, so please provide everything I would paste between “Sub FOO” and “End Sub” in the macro.

And yes, I know I should go and read 500 pages of documentation and learn how to do it myself but I don’t have the time right this second to do so.

Thank you very much in advance.

[oweng]

LO used its own Basic rather than VBA. To use VBA you are going to have to do something like what is mentioned in this answer. I don’t have a VBA-specific answer for, because I don’t have the knowledge. The Developer’s Guide Examples provide a Spreadsheet Sample with this related piece of code for setting cells:

    // --- Change cell properties. ---
    xPropSet = (com.sun.star.beans.XPropertySet)
        UnoRuntime.queryInterface( com.sun.star.beans.XPropertySet.class, xCell );

    // from table.CellProperties
    xPropSet.setPropertyValue( "IsCellBackgroundTransparent", new Boolean( false ) );
    xPropSet.setPropertyValue( "CellBackColor", new Integer( 0x99CCFF ) );

…and a General Table Sample for setting a range:

    // Accessing a cell range over its position.
    xCellRange = xSheet.getCellRangeByPosition( 2, 0, 3, 1 );

    // Change properties of the range.
    xPropSet = (com.sun.star.beans.XPropertySet)
        UnoRuntime.queryInterface( com.sun.star.beans.XPropertySet.class, xCellRange );
    xPropSet.setPropertyValue( "CellBackColor", new Integer( 0x8080FF ) );

    // Accessing a cell range over its name.
    xCellRange = xSheet.getCellRangeByName( "C4:D5" );

    // Change properties of the range.
    xPropSet = (com.sun.star.beans.XPropertySet)
        UnoRuntime.queryInterface( com.sun.star.beans.XPropertySet.class, xCellRange );
    xPropSet.setPropertyValue( "CellBackColor", new Integer( 0xFFFF80 ) );

Hopefully that will get you started.