[Solved] Change cell background colour based on call content

[krolwioski]

Hello,
I do have a problem as I can't use conditional formatting in that case as I do use more than 3 different values.

a
w
s
n
a/w

I would like to change cell background colour depending on the value that's inside of a cell like this:

a - Cyan 1
w - Black
s - Magenta 1
n - Yellow 1
a/w - Black
empty cell - WHITE FONT + WHITE BACKGROUND

I have read couple of threads on the forum but still it's like black magic for me, for example that one:
viewtopic.php?f=9&t=9697#p45472

Furthermore I do not want to lose my formula from column D "=IF(ISBLANK(S4);" ";S4)", so when there is no data instead of zero I does have blank cell with space. That could be swapped with additional value like: when cell does not have any data then set up font colour to white.

So I put my values manually in a column S, when column D return the same thing but with appropriate visual effect. Please see attached file.
Will anyone be able to help me with that?
Thank you!

[RoryOF]

There is an extension to extend Conditional Formatting at
https://extensions.openoffice.org/en/pr ... formatting

It may be that using .xls format will lose any such formatting. You should work in OO's native format, .ods for spreadsheets.

[krolwioski]

Thank you RoryOF!
Unfortunately I have to work with XLS format as I share those files quite often with people that use excel or different software, most of them do use XLS so I have too.
Will that extension work for someone else if I send file to someone else and that person will open that file on their PC?

EDIT:
Retarding above extensions looks like it's not working properly anyway as with every button press there's error showing up.

[krolwioski]

Ok I have found out solution.
Conditional formatting where: FORMULA IS: STYLE(D4)

Then add style named XXX, with all formatting as you would like to see it.

So when you put XXX in that cell it will take style with the same name as cell value.

EDIT:
It has to be ODS format. When I use XLS it does not change if I change cell data it keeps style that has been on that cell when saving to XLS format.