[Solved] Background change

[Islander206]

I have a sheet where some of the cells have Blue gray background and the rest of the cells have no fill.

I'd like to change background of all Blue gray cells to some other color. Is it possible to do this with Find & Replace or Conditional Formatting or something else?

[Zizi64]

I'd like to change background of all Blue gray cells to some other color. Is it possible to do this with Find & Replace or Conditional Formatting or something else?

Use Cell Styles instead manual (direct) formatting. Then you will able change properties all of Blue gray cells in the applied Cell Style.

(The Conditional Formatting function uses Cell Styles too...)

[Zizi64]

...or maybe you can write a macro for test and change the color in a selected cellrange.

example code snippet is in:
viewtopic.php?f=9&t=57066

Edit: You can determine the RGB code of the applied bakground color by an extension named "RGB": http://extensions.openoffice.org/fr/pro ... e/rgb-120l

[Islander206]

Would you please give me more details about how to find all Blue gray cells and change their background? They contain no numbers, no data, just plain text, different in each cell.

[acknak]

Just to be clear: there's no easy way to select/change all the cells with a background color. I wouldn't consider using a macro as "easy" unless you have a very large sheet.

You can select everything and remove the background, then apply a different color directly, or use a style as Tibor suggests, or use conditional formatting to apply a style.

[Islander206]

So, if I understand correctly, once background has been applied to a group of cells, there is no other way to change it beside to select the cells again manually and apply different background. But what if groups of such cells are scattered across a large sheet?
If this is a very difficult task, involving macros etc. - just forget it, it's not that important. But if it's possible to do it with just a few steps, why not? I just want to try different colors and see how it looks like.
Here is the sheet in question in the attachment.

BK-7m tone list.ods

(41.91 KiB) Downloaded 61 times

[Villeroy]

But what if groups of such cells are scattered across a large sheet?

... or even across multiple sheets?

This is what styles are about.
Call the stylist window (hit F11)
Ctrl+Click a single coloured cell.
Drag the highlighted cell into the stylist.
You are prompted for a name, say "Shaded".
Select manually all the blue cells

Edit: or select one cell and run the macro code of my next posting which will select all equally coloured

Double-click the "Shaded" entry.
Now all your equally formatted cells have this cell style named "Shaded".
Right-click>Edit... the entry, change colours, font, whatever you want.
The style change applies to all cells sharing this style within the same document.

Right-click>New... creates a new child copy of the style inheriting all properties of its parent. This way you can define formattings such as "just like "Shaded" but with/without ...".

[Zizi64]

Code: Select all

Sub ChangeBackgroundColor

oDoc = ThisComponent
oView = oDoc.getCurrentController()
oSheet= oView.getActiveSheet()
oSel = oDoc.getCurrentSelection()
oAddr = oSel.getRangeAddress()

nSCol = oAddr.StartColumn
nSRow = oAddr.StartRow
nECol = oAddr.EndColumn
nERow = oAddr.EndRow


	if (nERow-nSRow)>65536 or (nECol-nSCol)>1024   then
		Print "Error: too many cells selected"
		Exit sub
	end if

	sv = CreateUnoService("com.sun.star.ui.dialogs.ColorPicker")
	sv.execute = com.sun.star.ui.dialogs.ExecutableDialogResults.OK
	colorcode = sv.PropertyValues(0).Value 'colorcode: the decimal code of the selected new color
	sv.dispose

	for j = nSCol to nECol
		for i=nSRow to nERow	
			oCell = oSheet.getCellByPosition(j,i)
			if oCell.CellBackColor = RGB(230, 230, 255) then 'Search the desired (Bluegray) color
				oCell.CellBackColor = colorcode 'Change colors (equals to Searched color) to the selected color
			end if
		next 'i
	next 'j
End Sub

ChBackgColor.ods

(54.81 KiB) Downloaded 126 times

[Islander206]

Villeroy, thank you very much, this is what I need. It requires to select all my colored cells manually one more time, but just once. Instead of applying background directly to selected cells, I should apply a style first, then simply change that style's color.
Zizi64, your macro works flawlessly. No need to select cells by hand. There is one issue though: it affects Blue gray cells only. After the macro has been run, all cells changed their color and in order to run the macro again, we need to re-write it (if I understand correctly).
Anyway guys, many thanks for you help!

[Villeroy]

Select manually all the blue cells.

No, better select any such single cell and run this macro to select all equally coloured cells on the same sheet:

Code: Select all

Sub Select_Equal_BG_Color()
view = ThisComponent.getCurrentController()
sel = view.getSelection()
if not sel.supportsService("com.sun.star.sheet.SheetCell") then exit sub
basket = ThisComponent.createInstance("com.sun.star.sheet.SheetCellRanges")
col = sel.CellBackColor
sh = sel.getSpreadsheet()
ucf = sh.getUniqueCellFormatRanges()
e = ucf.createEnumeration()
while e.hasMoreElements()
	rgs = e.nextElement()
	if rgs.CellBackColor = col then basket.addRangeAddresses(rgs.getRangeAddresses(),False)
wend
view.select(basket)
End Sub

Then double-click your cell style.

[Zizi64]

Zizi64, your macro works flawlessly. No need to select cells by hand. There is one issue though: it affects Blue gray cells only. After the macro has been run, all cells changed their color and in order to run the macro again, we need to re-write it (if I understand correctly).

Yes, you must modify the code as you need it. For example you can use the color picker panel twice: once at select a color to "search", and again, when you select the "replace to" color. And then you need use the selected different (decimal) color codes (stored in two variable) for "search", and for "replace" procedure.

[Islander206]

Well, little too much for me... hey, I'm just a musician
It's never too late to learn!

[acknak]

Here's a shot at a reasonably well-styled sheet.

You can change the background (or any other property) of the highlighted cells by changing the _Highlight1 style:
Open the styles window: Format > Styles & Formatting
Right-click on _Highlight1 > Background, select the color you want; OK

Change any of the other styles likewise, to change the headings or Default for everything.

The styles still have to be applied manually. If there is some data or property that determines which ones should be highlighted, then it's best to have that data on the sheet itself.

[Islander206]

acknak, in your version of the file it's much easier to manipulate cell's background in groups.
It seems to me I start to understand style formatting better and better, thanks to the forum.