How to set Calc tab colour based on cell content?

I want to set tab colour based on value from a cell. Same thing like conditional formatting for cells, but for tab. For example I have a value in cell A1: if the value is below zero I want that tab to be red, if it’s above zero the tab colour should be green. If zero - grey.

What is the difficulty for you? Right clicking on a tab? Or writing a macro? If you change the value of cell A1 manually, why can’t you set the color of the tab at the same time?

It’s a 16 tab interlinked spreadsheet - I don’t want to do it manually every time I change something, so macro is probably the best way. I should ba able to handle setting up the macro to react to cell changes, but I can’t find how to change tab colour.

Why didn’t you immediately write about this in the text of the question? You can try the “.uno: SetTabBgColor” dispatcher command or set a sheet property - surprise! - TabColor

Just because I allowed a possibility that there is a function to do it without a macro. Thanks for help!

I failed with the dispatcher method, but I found something else…

Thank you. How do you get this code to automatically launch? I know how to do this using a form control and requiring a manual click. I would like to add a real time error checking ability that tells me which tabs have errors, if any. I could recreate the macro above without any problem. How do you get it to run automatically???

Macro that iterates over all sheets and sets tab colour to red or green base on value of A1 cell.

sub set_tab_colour_by_cell_value()
    all_sheets = ThisComponent.Sheets ' get all sheets
    ' iterate over sheets, looks like in the weird Basic it's safer to do it this way
    for i = all_sheets.getCount()-1 To 0 Step -1
	    a_sheet = all_sheets.getByIndex(i) ' get a sheet
	    cell_location = "A1" ' cell value to be used to set the tab colour
	    cell_value = a_sheet.getCellRangeByName(cell_location).Value ' get the cell value
	    new_colour = -1 ' -1 is the default colour
	    if cell_value < 0 then
		    new_colour = RGB(255, 0, 0) ' red
	    elseif cell_value > 0 then
		    new_colour = RGB(0, 255, 0) ' green
	    endif
	    a_sheet.tabcolor = new_colour ' set the tab colour
    next
end sub

Thank you for help JohnSUN!

Just one note - why .ActiveSheet? Perhaps it would be more correct to change the color of the tabs in the entire spreadsheet at one time For Each oSheet In ThisComponent.getSheets()?

I agree. That was just a proof of concept. I’ll improve the code using your suggestion.