Find and replace specific background color libre calc

[tcberry]

trying to replace one color with another in large spredsheet

[mariosv]

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

[librebel]

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

[JohnSUN]

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.

[librebel]

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

[RachelAB]

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).

[librebel]

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

[RachelAB]

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]

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

[librebel]

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.

[RachelAB]

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