+ Reply to Thread
Results 1 to 8 of 8

Conditionally format cells based on multiple conditions, comparison with adjacent cells

  1. #1
    Forum Contributor
    Join Date
    10-08-2014
    Location
    Bengaluru, India
    MS-Off Ver
    O 365
    Posts
    141

    Conditionally format cells based on multiple conditions, comparison with adjacent cells

    Hi everyone,
    in my excel table , i have project number on rows, months on columns and profit for each month and project in values.
    What i want to achieve is if the particular month profit goes up or down by 0.2 compared to previous month value, but it should not just see the previous month value it should also see the previous highlighted value , row no.5 (90025998) in attachment depicts the exact requirement
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2406 (Windows 11 23H2 64-bit)
    Posts
    82,546

    Re: Conditionally format cells based on multiple conditions, comparison with adjacent cel

    Explain EXACTKY why A5 needs to be highlighted. I do not understand your generic description above. Be specific, please.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Forum Contributor
    Join Date
    10-08-2014
    Location
    Bengaluru, India
    MS-Off Ver
    O 365
    Posts
    141

    Re: Conditionally format cells based on multiple conditions, comparison with adjacent cel

    Hi Ali, A5 need not to be highlighted , i meant that the cells in row number 5 shows the requirement like below
    Attached Images Attached Images

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2406 (Windows 11 23H2 64-bit)
    Posts
    82,546

    Re: Conditionally format cells based on multiple conditions, comparison with adjacent cel

    What requirement? You are not being specific enough in your explanation.

  5. #5
    Forum Contributor
    Join Date
    10-08-2014
    Location
    Bengaluru, India
    MS-Off Ver
    O 365
    Posts
    141

    Re: Conditionally format cells based on multiple conditions, comparison with adjacent cel

    In the screenshot above, lets look at the row number 5, in the first month which is 202301 profit is 1.57 and 2nd month which is 202302 profit is still 1.57 , then in the 3rd month 202303 profit becomes 1.85 so the difference between previous cell and the current cell is more than 0.2 so it should be highlighted with green colour and the symbol i am showing should come up. In the same way if value goes down by 0.2 it should be highlighted in red color. One more condition i want to apply here is the cell value should not just see the previous month value it should also see the previous highlighted value like in the same row number 5, column F which is 202305 , the value here is 1.65 if you compare that with previous cell its just gone down by 0.1 , however i want this to be compared with the previous highlighted value which is 202303 the value here is 1.85, the difference between these two is 0.2 , thats why 1.65 highlighted in red color
    Hope i am clear

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2406 (Windows 11 23H2 64-bit)
    Posts
    82,546

    Re: Conditionally format cells based on multiple conditions, comparison with adjacent cel

    Rules for C3:

    GREEN: =C3-B3>=0.2

    RED: =C3-B3<=0.2

    Both apply to: =$C$3:$H$6

    I don't think there's a way to add the arrowheads.
    Attached Files Attached Files

  7. #7
    Forum Contributor
    Join Date
    10-08-2014
    Location
    Bengaluru, India
    MS-Off Ver
    O 365
    Posts
    141

    Re: Conditionally format cells based on multiple conditions, comparison with adjacent cel

    Hi Ali, thank you for the formula, however in this my last condition is not applied;

    //One more condition i want to apply here is the cell value should not just see the previous month value it should also see the previous highlighted value like in the same row number 5, column F which is 202305 , the value here is 1.65 if you compare that with previous cell its just gone down by 0.1 , however i want this to be compared with the previous highlighted value which is 202303 the value here is 1.85, the difference between these two is 0.2 , thats why 1.65 highlighted in red color

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2406 (Windows 11 23H2 64-bit)
    Posts
    82,546

    Re: Conditionally format cells based on multiple conditions, comparison with adjacent cel

    Sorry - you cannot comapre with highlighted values. Excel cannot use text colour in a formula.

    I'm going to have to pass on this. I have given you what you described in post #1.

+ 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. using Formula to fill format cells conditionally based on cells
    By Stephen Buttle in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-18-2022, 06:20 AM
  2. Replies: 3
    Last Post: 07-21-2021, 12:44 AM
  3. Replies: 3
    Last Post: 01-24-2021, 11:33 AM
  4. Replies: 3
    Last Post: 06-28-2018, 11:20 PM
  5. Conditionally format adjacent Cells - if less than zero
    By rhiakatkat in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-26-2018, 03:31 PM
  6. Replies: 1
    Last Post: 01-20-2016, 08:56 AM
  7. Conditionally Format Row based on Multiple Cells in Row
    By todd.debacker in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-28-2007, 03:56 AM

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