Conditional Formatting. Highlight negative values. (Color, bracket, change sign, Italicise

Hi,
_ So
_ 1 ) I have a worksheet that is generated by large code ( which i would prefer not to change too much. )
_ This is just a simplified highly reduced data example
Using Excel 2007

-	A
1	100
2	200
3	300
4	400
5	500
6	600
7	700
8	800
9	-900
10	1000

Output

......
I am expecting only a small number of –ve values, if any. After reviewing the output i may decide to want to change the format of the sheet thus:
Using Excel 2007

-	A
1	100
2	200
3	300
4	400
5	500
6	600
7	700
8	800
9	(900)
10	1000

Output

_ so i am taking the minus off any –ve values, changing there color to red, putting brackets around it and making the Text format slanted ( italics i think you call that !? )
_ ...................
The following code does what i want.

Please Login or Register  to view this content.

_ But I am keen to learn and do it “right”. I am sure there must be a Type of conditioning format alternative I can apply to do this. I have basic VBA Knowledge and no experience what so ever with Conditional formatting. Googling suggested it would take me a month to learn how to do it. So I thought i would ask here for help first.
_ I want finally to do it with the macro. But I will gladly take any given steps to do it manually. I can then run a macro recording then to get the corresponding code. The Demos i found all showed me how to do pretty shading to show up different values in a range of values or just change the color, I want to do the change demonstrated.

. Any suggestions, VBA or some manual steps to get me going. Maybe there is a code line to apply the corresponding conditional formatting to the whole Output range to achieve the output I want

Thanks
Alan

Hi Alan,

Numbers are numbers
so storing them as strings is only rarely a wise approach. See green part of code with small ammendment which allows strings to be strings and numbers - numbers.

Then I used standard formatting with a custom format to get:
- red
- no minus sign
- and parentheses
(violet part )

And then conditional formatting to italicize output for negative values (blue part).

Please Login or Register  to view this content.

As a matter of fact, as you did .clear before,
this part:

Please Login or Register  to view this content.

could be even written as:

Please Login or Register  to view this content.

because there is only one format condition.

Hope this helps :-)

Thanks kaper for a very detailed and nicely shown reply.

Originally Posted by Kaper

....Numbers are numbers ..
so storing them as strings is only rarely a wise approach. See green part of code with small amendment ......)

.. often I tend to capture ranges in a “One Liner” with something like
arrIn()=rng.Value, So necessarily arrIn() must be Dim as Variant ( in the way you showed ) to meet the returned data Filed of Variant Elements. So i rarely have the chance to “control” the Type in my Array. When I do then it is just a sort of ( probably bad ) habit to want to control what I have, ( by narrowing the possibilities.. )
_
_ I have Found that Excel and VBA seems to be very well behaved in Turning / converting strings which look like numbers into the appropriate Long, Double etc that it wants when i give it a string. So if I have, as often do, data types that can be only Numbers or Strings, then rather than Variant I like to narrow it down a bit and use the String. ( I use Index a lot for example and it will happily take strings as arguments for its rows and columns as long as the look like Numbers, and also a Loop will take for example a start as a string of say 1.1 and happily convert that to the required Long of 1 etc.. etc... But you have a better idea than me - so maybe it is just another bad habit I should stop all that !! )
_ but that is all minor stuff....
_......................

_ I am really intrigued how your Conditional Formatting stuff ( or rather “custom format „ ) is working. Sorry to trouble you, but can you explain a bit how it is working. I have been experimenting and unsuccessfully attempting to Google out for several hours what is going on: In particular line 210 is puzzling me.
_ The point is , ( as you are thankfully using Named arguments, and as you explained well ), i have been able to guess what lines 220 - 260 are doing . ( So I added some ‘Green Comments to that effect.. If you have time maybe you could review and correct those ? )

_ Line 220 seems to be sensibly setting up the criteria for a Format Condition, held in some “FormatCondition Register listed by Item Number” somewhere internally by VBA....
_ Line 230 and 250 ( and 260 ) seem to refer to cells with that Format condition , that is to say cells maybe listed somewhere internally in that item number register..
_ That is all making some sense...

_ So what is really puzzling me is

Originally Posted by Kaper

.......a custom format to get:- red - no minus sign - and parentheses
(violet part )
.......

_ 1 ) How ( why ) does line 210 work where it is does – stepping through with F8 shows it does the magic, even though Line 220 has not been encountered jet ??
_ In fact ( and this is really sending me crazy... If I comment out lines from 220 then line 210 still does the main business. So how on earth can that happen ?? How is line 210 changing / selecting negative cells? I am totally mystified !?!


( _ 2 ) can you break that line 210 down a bit ? i tried to “open it up” by guessing, but never hit on any syntaxy correct longer alternatives )

Many Thanks Again
Alan

Please Login or Register  to view this content.

OK, with further calculations, numbers stored as texts would be interpreted (usually) right. But not at the formatting stage.

Going to 210 - yes it does most of work, except of italics for negative numbers
There are 4 parts of formatting string between semicolons. They are: positive;negative;zero;text
see fior instance https://support.office.com/en-au/art...2-09fab54be7f4
So positive value (it's important it is number, otherwise 4th part of formatting string would be used for any value) is displayed in general format, negative part is [Red](General), so we have it displayed as bare number surrounded by parentheses, no minus sign, everything in red.

Hi

Originally Posted by Kaper

OK, with further calculations, numbers stored as texts would be interpreted (usually) right. But not at the formatting stage.

.... Thanks, got it !!!!

Originally Posted by Kaper

Going to 210 - yes it does most of work, .......
There are 4 parts of formatting string between semicolons. They are: positive;negative;zero;text
..........

_ Thanks Kaper.
_ The link was very good, I had not after hours of searching found that. There is so much out there you really need the answer often to know where to look. So it is very helpful for experienced people like you to point us in the right direction ( They do give a strange example there.. That does not appear to work ?? ( [Blue]#,##0.00_);[Red](#,##0.00);0.00;"sales "@ ) ( Maybe "sales "@ or "sales " is something predefined they forgot to tell us about or it is a typo, and they meant
[Blue]#,##0.00_);[Red](#,##0.00);0.00;""sales ""@ .... which works.
Fair enough, “Quotes in Excel and VBA” is very difficult to understand..not many people do.....
http://www.mrexcel.com/forum/excel-q...l?#post4283381
http://www.excelfox.com/forum/f2/spe....html#post9517
_ ...............)

_ Anyways..

_ 1)
_ So what was throwing me off is that Code line 210 is a Code section which in itself defines as you say the format for how a number should look like depending on if it is either :
a Positive Number ; a Negative Number ; a Zero Number ; Or not a number at all but Text -
all very useful!!
_ I see from experimenting it is a declaration statement, that is to say you cannot do it more than once _ which then again makes sense:
_
_ So line 210 is a form of Conditional Formatting but restricted to the 4 option types. ( which are probably the most common and usual ) But this it is a sort of By – product, as you are actually defining ( declaring) how those basic forms of cell content should look like.


_2)
_ So Line 220 is one of maybe many ways to then “Get hold of “ or reference my –ve cells. You have chosen to do it by giving the format condition of a Type Value (xlCellValue ) and then that as less than ( xlLess ) to a formula ( "=0" ) of =0
_ I guess you could do something similar by selecting a the ( Red ) color ?

_
_ To help me to summarise point _1) and for anyone catching this Thread, I have modified my code so that my test Range will look initially like this before Line 210 is applied

Using Excel 2007

Row\Col	A
1	0
2	200
3	300
4	-400
5	500
6	600
7	Testie
8	800
9	-900
10	1000

Output

( Note row 4 has a Formula**** in it of
Using Excel 2007

-	A
4	=1 * -400

Output

_ ...........
_ I re choose Line 210 to make use of all 4 bits of the Code section,

" General ; [Red](##,##00.00) ; ""-"" ; ""You wrote: ""General "

And achieve approximately this after running the code below

Using Excel 2007

-	A
1	-
2	200
3	300
4	(400.00)
5	500
6	600
7	You wrote: Testie
8	800
9	(900.00)
10	1000

Output

Code :

Please Login or Register  to view this content.

And guess wot..... !!!!... The code will not quite work as I want ( Only giving the text format for the ; 4th text bit ) if I dimension my Array for Output as String rather than Variant ..... your advice was right, as was my answer

Originally Posted by Doc.AElstein

........ But you have a better idea than me - so maybe it is just another bad habit I should stop all that !! )........

... The Dimension as String put in the cells text for everything ( even the “Look like numbers” ), so I lose 1st ; 2nd ; and 3rd bits, as again you said...

Originally Posted by Kaper

....(it's important it is number, otherwise 4th part of formatting string would be used for any value)......

...words of wisdom again. ( I note that Thankfully it also reads as Numbers, generated by a formula in the Cell as Numbers**** and does the same formatting, which is convenient.. )

_ So I really have it now. Thanks for the jems of Info. ( I will mark the Thread as solved in a Day or too, just in case anyone has anything too add – bit it appears for me nicely wrapped up now )

Thanks very much Kaper
Alan