We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

How to set Calc tab colour based on cell content?

asked 2021-05-13 18:19:01 +0200

Przemo gravatar image

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.

edit retag flag offensive close merge delete



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?

JohnSUN gravatar imageJohnSUN ( 2021-05-13 18:39:05 +0200 )edit

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.

Przemo gravatar imagePrzemo ( 2021-05-13 18:45:34 +0200 )edit

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

JohnSUN gravatar imageJohnSUN ( 2021-05-13 19:19:53 +0200 )edit

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

Przemo gravatar imagePrzemo ( 2021-05-13 21:55:24 +0200 )edit

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

Przemo gravatar imagePrzemo ( 2021-05-13 21:56:27 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2021-05-13 21:55:58 +0200

Przemo gravatar image

updated 2021-05-14 17:45:20 +0200

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
        a_sheet.tabcolor = new_colour ' set the tab colour
end sub

Thank you for help JohnSUN!

edit flag offensive delete link more



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()?

JohnSUN gravatar imageJohnSUN ( 2021-05-14 05:45:53 +0200 )edit

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

Przemo gravatar imagePrzemo ( 2021-05-14 09:46:40 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2021-05-13 18:19:01 +0200

Seen: 35 times

Last updated: May 14