Ask Your Question
0

Filling/removing BG color of a cell range using VBA [closed]

asked 2013-05-07 21:24:05 +0200

Creqz gravatar image

updated 2013-05-08 00:27:47 +0200

oweng gravatar image

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.

edit retag flag offensive reopen merge delete

Closed for the following reason question is not relevant or outdated by Alex Kemp
close date 2015-10-31 04:22:08.536290

1 Answer

Sort by » oldest newest most voted
0

answered 2013-05-08 01:01:07 +0200

oweng gravatar image

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.

edit flag offensive delete link more

Question Tools

Stats

Asked: 2013-05-07 21:24:05 +0200

Seen: 1,659 times

Last updated: May 08 '13