+ Reply to Thread
Results 1 to 5 of 5

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

  1. #1
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    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
    Last edited by Doc.AElstein; 09-21-2015 at 03:58 AM.
    '_- Google first, like this _ site:ExcelForum.com Gamut
    Use Code Tags: Highlight code; click on the # icon above,
    Post screenshots COPYABLE to a Spredsheet; NOT IMAGES PLEASE
    http://www.excelforum.com/the-water-...ml#post4109080
    https://app.box.com/s/gjpa8mk8ko4vkwcke3ig2w8z2wkfvrtv
    http://excelmatters.com/excel-forums/ ( Scrolll down to bottom )

  2. #2
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often 365, but still time to time use also older versions
    Posts
    8,332

    Re: Conditional Formatting. Highlight negative values. (Color, bracket, change sign, Itali

    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 :-)
    Best Regards,

    Kaper

  3. #3
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Conditional Formatting. Highlight negative values. (Color, bracket, change sign, Itali

    Thanks kaper for a very detailed and nicely shown reply.
    Quote Originally Posted by Kaper View Post
    ....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
    Quote Originally Posted by Kaper View Post
    .......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.

  4. #4
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often 365, but still time to time use also older versions
    Posts
    8,332

    Re: Conditional Formatting. Highlight negative values. (Color, bracket, change sign, Itali

    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.
    Last edited by Kaper; 09-22-2015 at 07:31 AM.

  5. #5
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Conditional Formatting. Highlight negative values. (Color, bracket, change sign, Itali

    Hi
    Quote Originally Posted by Kaper View Post
    OK, with further calculations, numbers stored as texts would be interpreted (usually) right. But not at the formatting stage.
    .... Thanks, got it !!!!


    Quote Originally Posted by Kaper View Post
    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
    Quote Originally Posted by Doc.AElstein View Post
    ........ 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...
    Quote Originally Posted by Kaper View Post
    ....(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
    Last edited by Doc.AElstein; 09-22-2015 at 01:01 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Conditional Formatting - Positive/Negative Values
    By jamie1985 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-28-2015, 02:45 PM
  2. Replies: 4
    Last Post: 03-05-2014, 08:53 AM
  3. Conditional Formatting by VBA, Highlight color when If conditions satisfied
    By skhari in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-09-2012, 01:01 PM
  4. Conditional Formatting - Highlight the Top 2 values
    By HeebieGeebie in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-17-2012, 08:05 AM
  5. change the negative sign to brackets but no choice is available
    By the old guy in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-16-2006, 07:20 PM
  6. [SOLVED] apply conditional formatting to negative cell values
    By dona in forum Excel General
    Replies: 2
    Last Post: 03-10-2006, 08:10 AM
  7. Need conditional formatting formula to highlight top ten values i.
    By lightninbug in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 01-20-2005, 02:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1