Ask Your Question

find and replace specific background color libre calc

asked 2017-08-01 01:31:20 +0200

tcberry gravatar image

trying to replace one color with another in large spredsheet

edit retag flag offensive close merge delete


Please explain a bit how they are distributed, if they have the same style?

m.a.riosv gravatar imagem.a.riosv ( 2017-08-01 12:45:21 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2017-08-01 14:48:40 +0200

librebel gravatar image

updated 2017-08-01 15:59:42 +0200

Hello @tcberry,

To find and replace a specific background color in Calc, you could use the following macro:

Sub ReplaceBackgroundColor( lFindColor As Long, lReplaceColor As Long )
REM Changes the color of all cells with a given background color in ALL sheets within the current document.
REM <lFindColor>:       The Background color to be found and replaced, e.g. RGB(255,0,0).
REM <lReplaceColor>:    The new Background color for the found cells, e.g. RGB(255,255,255).
    Dim oSheets As Object, oSheet As Object
    Dim oFormatRanges As Object, oFormatRange As Object
    Dim i As Integer, j As Integer
    oSheets = ThisComponent.getSheets()     REM Traverse all sheets of the current document.
    For i = 0 To oSheets.getCount() - 1
        oSheet = oSheets.getByIndex( i )
        oFormatRanges = oSheet.getUniqueCellFormatRanges()  REM get ranges with the same format.
        For j = 0 To oFormatRanges.getCount() - 1
            oFormatRange = oFormatRanges.getByIndex( j )
            If oFormatRange.CellBackColor = lFindColor Then     REM Found Target Color.
                oFormatRange.CellBackColor = lReplaceColor
            End If
        Next j
    Next i
End Sub

EDITED 2017-08-01

Removed line "Exit For" , thanks @JohnSUN

edit flag offensive delete link more


Good work, @librebel ! But the line Exit For is not necessary. GetUniqueCellFormatRanges takes into account the font format as well. That is, if cells with a given background contain, for example, a normal and bold font, then the background change will not be complete.

JohnSUN gravatar imageJohnSUN ( 2017-08-01 15:45:58 +0200 )edit

Thank you @JohnSUN, i removed that line from the code.

librebel gravatar imagelibrebel ( 2017-08-01 16:00:13 +0200 )edit

Thanks for the macro! How do I use it on a range of cells only? I have a sheet that contains a bunch of cells in RGB(0,106,199) but only want to replace a certain subset of them with RGB(255,0,0) (i.e., turn the blue background to red).

RachelAB gravatar imageRachelAB ( 2017-11-20 00:30:12 +0200 )edit

Yw @RachelAB,

To change the background color of a particular range, you could just use the following macro:

Sub cellRange_SetBackgroundColor( strRange as String, lBackgroundColor as Long )
REM Example call: cellRange_SetBackgroundColor( "A14:D14", RGB(255,0,0) )
    Dim oSheet As Object  : oSheet = ThisComponent.CurrentController.ActiveSheet
    Dim oRange As Object  : oRange = oSheet.getCellRangebyName( strRange )
    oRange.CellBackColor  = lBackgroundColor
End Sub
librebel gravatar imagelibrebel ( 2017-11-20 00:56:36 +0200 )edit

This works to color cells A14:D14 red but it doesn't replace (say) cell B14's blue background with red, which is what I am trying to do. I'll need to loop over the cells but haven't gotten this from original to work in your new code (replacing 2nd to last line):

For j = 0 To oRange.getCount() - 1
    oRange = oRange.getByIndex( j )
    If oRange.CellBackColor = lFindColor Then     REM Found Target Color.
       oRange.CellBackColor = lReplaceColor
    End If
Next j
RachelAB gravatar imageRachelAB ( 2017-11-20 06:43:17 +0200 )edit

I get a "BASIC runtime error. Property or method not found: getCount." (using LibreOffice on MacOS High Sierra)

RachelAB gravatar imageRachelAB ( 2017-11-20 06:46:54 +0200 )edit

Hello @ RachelAB,

getCount() was for the cellRanges object, not for a single Range.

The new method that i made is larger than the available space in this comment box; please start a new question then i'll post it there.

librebel gravatar imagelibrebel ( 2017-11-20 10:24:17 +0200 )edit

Thanks, @librebel, I appreciate your help! I created a new question here.

RachelAB gravatar imageRachelAB ( 2017-11-21 05:47:44 +0200 )edit
Login/Signup to Answer

Question Tools



Asked: 2017-08-01 01:31:20 +0200

Seen: 921 times

Last updated: Aug 01 '17