On 08/28/2010 09:49 PM, Johnny Rosenberg wrote:
2010/8/28 Andy Chaplin<[email protected]>:
On 08/28/2010 05:45 PM, Johnny Rosenberg wrote:
2010/8/28 Jean-Baptiste Faure<[email protected]>:
Le 28/08/2010 16:44, Andy Chaplin a écrit :
Hi all
As far as I can see it's only possible to have 3 levels of conditional
formatting. Does anyone know of a trick or workaround to get more
conditions to apply. Ideally i would like six and any suggestions
would be gratefully appreciated.
You can use formula with nested IF() and STYLE() functions.
JBF
Absolutely.
And if you want different formats for different text strings, such as
names if people or something, you can create a style with the same
name for each person and just add the style directly… For example:
=Sheet1.A1 + STYLE(A1)
Except that if Sheet1.A1 is empty, 0 will be displayed rather than an
empty cell.
The following might work:
=IF(Sheet1.A1="";"";STYLE(A1))
But I don't know if this is doable in Andy’s situation.
Regards
Johnny Rosenberg
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
Thanks to you both for your prompt replies (which actually solve another
problem I was working on!)
In this case, however, it doesn't seem to do what I want (which I didn't
explain in my original query).
What I have is a table with parts listed vertically and dates horizontally.
Each part can be in 1 of 6 different states on any given day. What I'm
trying to do is to set up a sheet whereby if you put, for example, A in a
cell the backgound is red, B is blue, C is green, D is yellow and so on.
... actually, as I've been typing this, I've worked out that this could be
done using your proposal by having a data entry sheet and a separate sheet
for display and nested IFs, but is there a way of doing this without the
intermediate sheet?
You don't need any IFs if you just name your styles properly, but I
understand what you mean. Unfortunately I didn't find a solution to
this problem when I experimented with this a few years ago, but if I
recall correctly I filed a feature request about this, I think.
If you are familiar with OpenOffice.org Basic programming you could
write a macro that solves your problem. Since you only need six
different states you could write a macro that does the following:
— Open a dialogue box with six radio buttons marked A, B, C, D, E and F.
— When you selected one of them by either click it or hit a key on
your keyboard, for example A, and you then hit Enter or click OK, the
letter will automatically be filled in the currently selected cell and
the background colour will automatically be adjusted.
— You could associate the macro with a keyboard combination, such as Ctrl+Alt+a.
Another suggestion is to write six macros, one for each state. One
fills in an A and the right colour for A, one does the same for B. No
dialogue bos is then required and you associate the macros to six
different keyboard combinations. I found that these six combinations
are not taken, at least not on my system:
Alt+a
Alt+b
Alt+c
Alt+d
Alt+e
Alt+f
Also, these combinations are not taken:
Ctrl+Alt+a
Ctrl+Alt+b
Ctrl+Alt+c
Ctrl+Alt+d
Ctrl+Alt+e
Ctrl+Alt+f
Isn't that very convenient…?
Here are the macros required, I have tested them. They also require
that you create six styles named A, B, C and so on. That makes it more
flexible, so you can change colours of backgrounds, characters etc,
without having to change the code.
REM ***** BASIC *****
Option Explicit
Sub A
setCellProperties("A")
End Sub
Sub B
setCellProperties("B")
End Sub
Sub C
setCellProperties("C")
End Sub
Sub D
setCellProperties("D")
End Sub
Sub E
setCellProperties("E")
End Sub
Sub F
setCellProperties("F")
End Sub
Sub setCellProperties(State As String)
Dim oCell As Object
oCell=ThisComponent.getCurrentController().getSelection()
If oCell.SupportsService("com.sun.star.sheet.SheetCell") Then '
Selection is a single cell
oCell.setString(State)
oCell.setPropertyValue("CellStyle",State)
Else
MsgBox "Please select one cell only!"
EndIf
End Sub
So what happens, when you associate Alt+A with the macro A and so on,
is that when you hit Alt+A, style A is applied to the selected cell
and an ”A” is also entered in the cell automatically. So the extra
work for you when you set everything up, is that you have to hold the
Alt key when you enter your letter. Maybe that is an acceptable
compromise?
Kind regards
Johnny Rosenberg
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
WOW!
Thank you so much for that. I've got a lot on my plate for today, but
I'll try it out either this evening or tomorrow and get back to you.
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
