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

Ask Your Question
0

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

Comments

1

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
1

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
0

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

Thank you for help JohnSUN!

edit flag offensive delete link more

Comments

1

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

Stats

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

Seen: 35 times

Last updated: May 14