+ Reply to Thread
Results 1 to 6 of 6

Conditional Formatting in Pairs not working.

  1. #1
    Forum Contributor FRIEL's Avatar
    Join Date
    03-07-2008
    Location
    Coventry
    MS-Off Ver
    2003 and 2007
    Posts
    266

    Conditional Formatting in Pairs not working.

    Hey All,
    I have been having some problems with some conditional formatting.

    I have columns showing Foretasted Spend vs Actual Spend (Starting with D:E and going down and across)
    What I am after, is comparing 2 things:
    • Weather the Forcast is more than a Actual By a certain Percentage shown in [A3]
    • If the Forecast is lower than actual
    Now i have the below formula that works in a cell but doesn't seem to work in the conditional formatting.

    Cross Posted here:
    https://www.mrexcel.com/forum/excel-...ml#post4929882
    Last edited by FRIEL; 10-16-2017 at 05:31 AM.
    I dont like to use code i dont understand
    it makes it hard to use in other situations
    so please try to be as clear and patent as possible with me

    Criticism is welcomed

  2. #2
    Forum Contributor FRIEL's Avatar
    Join Date
    03-07-2008
    Location
    Coventry
    MS-Off Ver
    2003 and 2007
    Posts
    266

    Re: Conditional Formatting in Pairs not working.

    for some reason i cant Post my formula, due to HTML being blocked?

  3. #3
    Forum Contributor FRIEL's Avatar
    Join Date
    03-07-2008
    Location
    Coventry
    MS-Off Ver
    2003 and 2007
    Posts
    266

    Re: Conditional Formatting in Pairs not working.

    https://pastebin.com/dbVpnHDs

  4. #4
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,684

    Re: Conditional Formatting in Pairs not working.

    what cell is actual and what cells is forecast ?
    D or E

    Assuming that Actual is D and forecast is E

    A3 is the fixed %

    you can use a formula in conditional formatting

    Select columns D and E
    and assuming the A3 is formatted as % and so contains 0.2 showing as 20%

    =OR( $D1 > $E1 , 1-($D1/$E1)>($A$3/100))
    Wayne
    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  5. #5
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Conditional Formatting in Pairs not working.

    Quote Originally Posted by FRIEL View Post
    for some reason i cant Post my formula, due to HTML being blocked?
    If this happens, put spaces around any inequality signs.

    e.g. change =A2>B2 to =A2 > B2

  6. #6
    Forum Contributor FRIEL's Avatar
    Join Date
    03-07-2008
    Location
    Coventry
    MS-Off Ver
    2003 and 2007
    Posts
    266

    Re: Conditional Formatting in Pairs not working.

    Sorry for the late reply, i was unavailable yesterday.

    Quote Originally Posted by 63falcondude View Post
    If this happens, put spaces around any inequality signs.

    e.g. change =A2>B2 to =A2 > B2
    Thats good to know thanks.




    Quote Originally Posted by etaf View Post
    what cell is actual and what cells is forecast ?
    D or E

    Assuming that Actual is D and forecast is E

    A3 is the fixed %

    you can use a formula in conditional formatting

    Select columns D and E
    and assuming the A3 is formatted as % and so contains 0.2 showing as 20%

    =OR( $D1 > $E1 , 1-($D1/$E1)>($A$3/100))
    No, this wont work as the data goes beyond columns D & E they are just the first pair.



    It would have been nice to get this working but I now have a workaround, though its not exactly pretty.

    I've created a second worksheet with all corresponding cells containing the logic formula to check my values on the front sheet are within tolerance.
    now my conditional formatting looks at this sheet to check if the same cell on the front sheet should be red.
    I populate these formulas when the front sheet is populated (by VBA)

+ 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 not working
    By mnhim001 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-05-2015, 02:26 PM
  2. [SOLVED] Conditional formatting not working?
    By Rheanna in forum Excel General
    Replies: 0
    Last Post: 06-24-2015, 11:49 AM
  3. [SOLVED] Conditional formatting is not working.
    By opusarlo in forum Excel General
    Replies: 3
    Last Post: 05-08-2015, 10:39 AM
  4. Conditional Formatting based on sum of pairs of cells.
    By DylanTheHun in forum Excel General
    Replies: 7
    Last Post: 11-14-2013, 12:47 PM
  5. conditional formatting to find absolute value pairs
    By kisboros in forum Excel General
    Replies: 2
    Last Post: 01-04-2013, 02:51 AM
  6. VBA working with conditional formatting
    By MARKSTRO in forum Excel Programming / VBA / Macros
    Replies: 31
    Last Post: 01-12-2012, 05:33 PM
  7. VBA conditional formatting not working
    By LarryC in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-26-2008, 09:43 AM

Tags for this Thread

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