Calc Number Color

English
#1

I have a macro which I use to format a group of cells in Calc. It uses the following code (which I recorded) to change negative numbers from BLACK (default) to RED:

rem ----------------------------------------------------------------------

dim args6(0) as new com.sun.star.beans.PropertyValue

args6(0).Name = “NumberFormatValue”

args6(0).Value = 131

dispatcher.executeDispatch(document, “.uno:NumberFormatValue”, “”, 0, args6())

The code for number Negative number format from “Format Cells” is:

#,###.00;[RED]-#,###.00

which bears no resemblance to “Value = 131” in the code above.

Does anyone know what the Value should be to make positive numbers in a group of cells GREEN, i.e.,

argsx(0).Name = “NumberFormatValue”

argsx(0).Value = ???

I have not been able to find any way to do that other than going through the Format Cells→Font Effects→Font Color and selecting Green, or picking a Cell that has positive numbers and alreaady formated with the Green color using the Clone Format choice.

Both methods are cumberson and neither seem to be able to be recorded to create a macro. The above lines of code would be so much simpler to implement, or could there be a choice in Number Format to chose a color for positive numbers in addition to Selecting Negative Numbers Red.

#2

Have you tried using Conditional Formatting?

#3

Why don’t you just create a cell style that you can apply by selecting a range and double-clicking? That is why Calc has cell styles. Of course you are stuck with RED for negative (or one of the following: CYAN, GREEN, BLACK, BLUE, MAGENTA, RED, WHITE, YELLOW) but no reason not to make postive numbers any colour that you like, see Number Format Codes

#4

Use the Styles (Cell Styles) instead of the direct (manual) formatting method.
You can apply the stales manually, or by macros.
You must use styles in the Conditional Formatting feature.

#6

Hi, @dbondo ,

Have you tried using Format Cell?

But be careful, when formatting in color, the negative does not show the sign, if you want it visible, it is necessary to include it in the format.

FORMATAÇÃO

#5

Thanks, I’ve created a Style and named it in Conditional Formating. I Can get to it manually and enforce the style on any group of selected cells in a Calc spreadsheet.

Hopefully I ill be able to record a macro to do that automatically. I will be trying to do that soon.

#7

Only 7 named colours do CALC accept for letters, numbers:

  • [BLACK],
  • [WHiTE],
  • [GREY,GRAY],
  • [BLUE],
  • [GREEN],
  • [YELLOW],
  • [BROWN],
  • [RED] and
  • [CYAN].

Try this format as an example:
[<0][RED]#.###;[>2][BLUE]#,###.#;standard
you have to declare 3 cases with 2 semicolons, automatically by adding the minus sign if a negative number in red, greater than 2 in blue, between 0 and 2 in standard-colour of the system.

#8

I used conditional format as suggested to get the Negative numbers Red and the Positive numbers Green. However I discovered that I couldn’t get it recorded in q Macro since record macro doesn’t recognize mouse selections.

The information changes business day and is placed in a new location when recorded

My solution to make a macro work was to copy the cells which had been conditionally styled into a specific location on a seperate sheet in the calc spreadsheet. That way I could access that specificgroup of cells by recording key strokes and then to Paste Special (Ctrl+Shift+V) with Format Only into the group of newly created info in the new group of cells of the of the base spreadsheet. Here is an example of the condional formatted cells:

Examp[e -99.06 -0.26
Examp[e 55.97 0.36
Examp[e 3.95 0.08
Examp[e -14.40 -0.73
Examp[e -0.31 -0.88
Examp[e -0.40 -1.24
Examp[e -0.06 -0.22
Examp[e -0.70 -1.25
Examp[e 0.34 0.44
Examp[e -0.20 -0.90

Here is a copy of the Macro that was created and placed as a suproutine into my major Macro for updating that spreadsheet with everything in the right location:

sub PosGreenNegRed

rem ----------------------------------------------------------------------

rem define variables

dim document as object

dim dispatcher as object

rem ----------------------------------------------------------------------

rem get access to the document

document = ThisComponent.CurrentController.Frame

dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------

dispatcher.executeDispatch(document, ".uno:JumpToNextTable", "", 0, Array())

rem ----------------------------------------------------------------------

dim args1(0) as new com.sun.star.beans.PropertyValue

args1(0).Name = "ToPoint"

args1(0).Value = "$A$10"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())

rem ----------------------------------------------------------------------

dim args2(0) as new com.sun.star.beans.PropertyValue

args2(0).Name = "By"

args2(0).Value = 1

dispatcher.executeDispatch(document, ".uno:GoRightToEndOfDataSel", "", 0, args2())

rem ----------------------------------------------------------------------

dim args3(0) as new com.sun.star.beans.PropertyValue

args3(0).Name = "By"

args3(0).Value = 1

dispatcher.executeDispatch(document, ".uno:GoDownToEndOfDataSel", "", 0, args3())

rem ----------------------------------------------------------------------

dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())

rem ----------------------------------------------------------------------

dispatcher.executeDispatch(document, ".uno:JumpToPrevTable", "", 0, Array())

rem ----------------------------------------------------------------------

dim args4(1) as new com.sun.star.beans.PropertyValue

args4(0).Name = "By"

args4(0).Value = 3

args4(1).Name = "Sel"

args4(1).Value = false

dispatcher.executeDispatch(document, ".uno:GoLeft", "", 0, args4())

rem ----------------------------------------------------------------------

dim args6(1) as new com.sun.star.beans.PropertyValue

args6(0).Name = "By"

args6(0).Value = 1

args6(1).Name = "Sel"

args6(1).Value = false

dispatcher.executeDispatch(document, ".uno:GoUpToStartOfData", "", 0, args6())

rem ----------------------------------------------------------------------

dim args7(5) as new com.sun.star.beans.PropertyValue

args7(0).Name = "Flags"

args7(0).Value = "T"

args7(1).Name = "FormulaCommand"

args7(1).Value = 0

args7(2).Name = "SkipEmptyCells"

args7(2).Value = false

args7(3).Name = "Transpose"

args7(3).Value = false

args7(4).Name = "AsLink"

args7(4).Value = false

args7(5).Name = "MoveMode"

args7(5).Value = 4

dispatcher.executeDispatch(document, ".uno:InsertContents", "", 0, args7())

rem ----------------------------------------------------------------------

dim args8(0) as new com.sun.star.beans.PropertyValue

args8(0).Name = "By"

args8(0).Value = 9

dispatcher.executeDispatch(document, ".uno:GoDownSel", "", 0, args8())

rem ----------------------------------------------------------------------

dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())

end sub

The reason for all the above was that I didn’t want to assign a key stroke to the conditional formating within my major upcating macro.

Thanks everyone for all your comments and help in getting me to this solution.

[erAck: edited to format code as code, see This is the guide - How to use the Ask site? - #6 by erAck]

#9

Sorry that my example didn’t come through in colors. But it does work beautifully.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.