Results 1 to 6 of 6
  1. #1
    5 Star Lounger petesmst's Avatar
    Join Date
    Dec 2009
    Location
    Cape Town, South Africa
    Posts
    789
    Thanks
    38
    Thanked 43 Times in 33 Posts

    Can't Solve a Simple Conditional Formatting Issue: Help?

    I am stuck with a simple problem (Excel 2013), but just can't get a formula to work (must be an age thing!!)

    I have a series of columns side by side, each containing currency totals. I wish to compare the numbers in each column with the corresponding number of the cell immediately to the left and apply conditional formatting as follows:

    If number in Cell B1 is greater than or equal to that in Cell A1, format number in Cell B1 in red with pale red fill; if Cell B1 is smaller than that in A1, format number in Cell B1 in green with pale green fill. And the same for B2 vs A2, B3 vs A3 etc. Similarly comparing C1 to B1; C2 to B2 etc; and D1 to C1; D2 to C2; etc. New columns will be added monthly. About 20 rows to each column.

    So far I have only been able to accomplish what I want by applying conditional formatting one cell at a time; I just can't get a formula to work that can be applied to all columns/rows.

    (My Setup: Custom built: 3,70GHz Intel Core i7-4820K CPU; MSI Military Class iii X79A-GD45 Plus Motherboard; Win 10 Pro (64 bit) - (UEFI-booted); 16GB RAM; 512GB SAMSUNG SD850 PRO SSD; 120GB SAMSUNG 840 SSD; Seagate 2TB Barracuda SATA6G HDD; GeForceGTX 980 4GB Graphics Card; Office 2013 Prof (32-bit); MS Project 2013 (32-bit); Acronis TI 2015 Premium, NIS 2016, VMWare Workstation12 Pro, etc). WD My Book 3 1TB USB External Backup Drive). Samsung 24" Curved HD Monitor.

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,819
    Thanks
    133
    Thanked 480 Times in 457 Posts
    Hi

    First of all, format the required range of numbers with a default pale red fill and red text.
    Next, create a conditional formatting rule as a 'relative' formula (i.e. don't use $B$1 etc):
    =B1<A1
    ..where the format is pale green background and bold green text.
    For the Applies to range, specify the required range (this time, using absolute refs), for example:
    =$B$1:$H$20

    See my attached example.
    (I've included a header row, just to show the difference in set up)

    zeddy
    Attached Files Attached Files

  3. The Following User Says Thank You to zeddy For This Useful Post:

    petesmst (2016-08-28)

  4. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 645 Times in 589 Posts
    New columns will be added monthly. About 20 rows to each column
    Nicely done zeddy but your method will require monthly maintenance to set the next month's column cell fill to pale red with red text and then extending the "Applies to" range to cover it.

    perhaps, the approach of having no default cell fill with three CF rules might be better:

    1. No cell fill for range B2:Z21
    2. Highlight B2 to Z21
    3. create your rules to the following:

    petesmst1.png

    Note the "Stop if True" for the first rule

    Each month the user just drops in the new month's numbers and the formatting will be automatically applied.

    HTH,
    Maud

  5. The Following User Says Thank You to Maudibe For This Useful Post:

    petesmst (2016-08-28)

  6. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,819
    Thanks
    133
    Thanked 480 Times in 457 Posts
    Hi Maud

    To avoid 'monthly maintenance' my range could be extended to column [Z] too.
    The main reason I was trying to keep the number of rules to a minimum (i.e. just 1, rather than 3) is to mitigate the conditional formatting nightmare that occurs when any extra columns, rows etc etc etc get inserted into an existing range. The number of conditional formatting 'rules' for the sheet then grows ridiculously large, fragmented and unmanageable.

    It is a pity that we still cannot define 'dynamic ranges' for the 'applies to' range of a conditional formatting rule.
    If you try to do this, Excel will immediately 'convert' any attempt to use a dynamic range name to an 'absolute range address'. This is because Conditional Formats are a Property of the Range object, and Range objects cannot have addresses defined by a formula (it needs to be a Reference)

    I believe the ideal solution is to use vba to manage the conditional formatting on a sheet.
    I usually use an Event trigger to 'clear-and-reset' conditional formatting rules on a sheet.

    zeddy

  7. The Following User Says Thank You to zeddy For This Useful Post:

    petesmst (2016-08-28)

  8. #5
    5 Star Lounger petesmst's Avatar
    Join Date
    Dec 2009
    Location
    Cape Town, South Africa
    Posts
    789
    Thanks
    38
    Thanked 43 Times in 33 Posts
    @zeddy: Many thanks; it works for me!
    (My Setup: Custom built: 3,70GHz Intel Core i7-4820K CPU; MSI Military Class iii X79A-GD45 Plus Motherboard; Win 10 Pro (64 bit) - (UEFI-booted); 16GB RAM; 512GB SAMSUNG SD850 PRO SSD; 120GB SAMSUNG 840 SSD; Seagate 2TB Barracuda SATA6G HDD; GeForceGTX 980 4GB Graphics Card; Office 2013 Prof (32-bit); MS Project 2013 (32-bit); Acronis TI 2015 Premium, NIS 2016, VMWare Workstation12 Pro, etc). WD My Book 3 1TB USB External Backup Drive). Samsung 24" Curved HD Monitor.

  9. #6
    5 Star Lounger petesmst's Avatar
    Join Date
    Dec 2009
    Location
    Cape Town, South Africa
    Posts
    789
    Thanks
    38
    Thanked 43 Times in 33 Posts
    @Maudibe: Thanks a ton; your refinement is excellent. (I just could not sort out the sequence of steps to get my own attempt to work). Much appreciated.
    (My Setup: Custom built: 3,70GHz Intel Core i7-4820K CPU; MSI Military Class iii X79A-GD45 Plus Motherboard; Win 10 Pro (64 bit) - (UEFI-booted); 16GB RAM; 512GB SAMSUNG SD850 PRO SSD; 120GB SAMSUNG 840 SSD; Seagate 2TB Barracuda SATA6G HDD; GeForceGTX 980 4GB Graphics Card; Office 2013 Prof (32-bit); MS Project 2013 (32-bit); Acronis TI 2015 Premium, NIS 2016, VMWare Workstation12 Pro, etc). WD My Book 3 1TB USB External Backup Drive). Samsung 24" Curved HD Monitor.

Posting Permissions

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