On 08/29/2010 11:35 AM, Johnny Rosenberg wrote:
2010/8/29 Andy Chaplin<[email protected]>:
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.
I am actually working on another solution right now with listeners.
That would be very convenient if it works. There seems to be some
known difficulties though, so I guess there need to be some ”ugly”
code involved.
The data you want to colour, where is it located? Is all of it in the
same row or the same column? In that case, which one? The problem is
that you can assign a listener to a specific cell, but then you need
one listener per cell. You can also assign one listener to a specific
cell range, but then you don't know which cell was changed, only that
one (or more) in that range was changed, so I somehow need to compare
”before change” and ”after change” which could be time consuming. On
the other hand, if the range isn't too big, the macro could just go
through every cell in the range and set the colours accordingly.
Regards
Johnny Rosenberg
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
Hi there
I have a file with around 20 sheets. Each sheet is an account. Each
account has between 10 and 30 parts listed vertically. An account
period lasts for no more than 100 days. The dates are listed
horizontally. In this case the primary use is to read the data in rows
sow as to get a quick visual overview of the status of each part. Each
day is a snapshot, so once it's entered it doesn't change. The object
is to get a quick automatic colour-coding of each entry. Up to now
we've been using coloured strips on a magnetic whiteboard, but the
volume has increased dramatically in the past few weeks and the only
option now is to digitalise it.
It looks like one of those things which should be quite straightforward,
but in practice is a bit tougher! ;o)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
