+ Reply to Thread
Results 1 to 9 of 9

Conditional format cells in column, RAG, based on preceding cell value

  1. #1
    Registered User
    Join Date
    10-29-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    80

    Conditional format cells in column, RAG, based on preceding cell value

    I have an excel sheet containing a variable number of columns (currently G3:AX30) I want to fill each cell with a colour (Red Yellow or Green) based on whether the cell value is higher than, the same as or lower than the value in the cell above it.
    My initial attempts using CF in 2003 all seem to point at G3 and use this value as the conditon for applying the conditonal formatting instead of looking at the cell value and comparing it with the value in the cell above it.

    Many thanks

  2. #2
    Forum Expert
    Join Date
    09-20-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    2,278

    Re: Conditional format cells in column, RAG, based on preceding cell value

    Hello
    Not sure what formatting you want in the top row but try selecting G4:AX30 and applying the CF as in the attached image.

    CF Dialogue.JPG

    Hope this helps.
    DBY

  3. #3
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2019
    Posts
    1,796

    Re: Conditional format cells in column, RAG, based on preceding cell value

    Hi,

    attached an example

    =AND(G4<>"",G4>G3) red (maybe green)

    =AND(G4<>"",G4=G3) yellow

    =AND(G4<>"",G4<G3) green (maybe red)

    In order to extend the formatting rules (if they are ok) just copy G4 and paste special format to new ranges.

    Regards


    edit: sorry DBY, I did not mean to overlap.

    Regards
    Attached Files Attached Files
    -----------------------------------------------------

    At Excelforum, you can say "Thank you!" by clicking the star icon ("Add Reputation") below the post.

    Please, mark your thread [SOLVED] if you received your answer.

  4. #4
    Registered User
    Join Date
    10-29-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    80

    Re: Conditional format cells in column, RAG, based on preceding cell value

    Thanks for the assistance. That's almost what I'm after. I forgot to mention (which always complicates things) that not all the cells in each column in are filled consecutively with a value. If there is no value in the cell then it should remain unfilled - but I still need any further values to look at the last positive value to make the comparision.
    Hope this makes sense.

  5. #5
    Forum Expert
    Join Date
    09-20-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    2,278

    Re: Conditional format cells in column, RAG, based on preceding cell value

    Sorry I couldn't reply earlier, things to do! It's a bit of a challenge but I've come up with the following CF formulas:

    For the greater than:

    Please Login or Register  to view this content.
    Equals to:

    Please Login or Register  to view this content.
    Less than:

    Please Login or Register  to view this content.
    As before I'm not sure about the top row but apply these to G4:AX30. Blank cells should be ignored although it recognizes zeros. Also the Match function looks for an extreme value of 10^10, I'm assuming there are no numbers larger than this in the range, if so make it bigger.

    Not sure if it all works but try it out.
    DBY

  6. #6
    Registered User
    Join Date
    10-29-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    80

    Re: Conditional format cells in column, RAG, based on preceding cell value

    DBY
    This is great stuff. It almost works as advertised... (but this is no fault on your part and the exceptional work you have done for me). When implimenting your solution, it didn't appear to work correctly at first, but on investigation it seems that my 'blank' cells actually contain a hidden zero.(They are populated from elsewhere by a complex formula).
    In your CF formula I substituted "" for the 0 which had the appearance of working but cells that were occupied further down the columns were showing incorrect colour formatting. I believe that this is due to the fact that the cell is not actually empty but contains a hidden value of zero which is then used as the comparitor in the CF formula rather than the last proper number.

    Just need to find a way to strip those zero's out now.

    I'm not sure if I should close this as solved or if the hidden zero's can be taken into account in the CF formula.

    Many thanks

  7. #7
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2019
    Posts
    1,796

    Re: Conditional format cells in column, RAG, based on preceding cell value

    Hi again,

    I'm playing with DBY's CF rules

    In G4:

    for the greater than:

    =AND(G4<>"",ISNUMBER(LOOKUP(2,1/(G$3:G3<>0),G$3:G3)),G4>LOOKUP(2,1/(G$3:G3<>0),G$3:G3))

    equals to:

    =AND(G4<>"",ISNUMBER(LOOKUP(2,1/(G$3:G3<>0),G$3:G3)),G4=LOOKUP(2,1/(G$3:G3<>0),G$3:G3))

    lesser than:

    =AND(G4<>"",ISNUMBER(LOOKUP(2,1/(G$3:G3<>0),G$3:G3)),G4<LOOKUP(2,1/(G$3:G3<>0),G$3:G3))


    Hope they work

  8. #8
    Forum Expert
    Join Date
    09-20-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    2,278

    Re: Conditional format cells in column, RAG, based on preceding cell value

    Thanks CANAPONE. I'd been working on a possible solution before I saw your reply but this seems to work brilliantly. I'd been trying to suppress the CF when encountering zeros as well as blank cells, if this is needed, a small addition to the 'lesser than' AND criteria seems to work:

    Please Login or Register  to view this content.
    DBY

  9. #9
    Registered User
    Join Date
    10-29-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    80

    Re: Conditional format cells in column, RAG, based on preceding cell value

    Great work guys. I don't know how you work this magic... it's all voodoo to me... but whatever it is you do, keep doing it. People like me will always be indebted to people like you who offer their skill, expertise and time to assist us lesser mortals.

    Thank-you once again !

+ 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. Replies: 7
    Last Post: 08-14-2013, 05:41 AM
  2. [SOLVED] conditional format row based on cell in column A
    By ea223 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-22-2013, 11:36 AM
  3. [SOLVED] Conditional Format column based on value of a range of cells.
    By pauld.ccvw in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-27-2012, 09:35 AM
  4. Replies: 3
    Last Post: 02-21-2011, 11:29 AM
  5. Conditional Format 1 cell based on other cells
    By dbrine in forum Excel General
    Replies: 7
    Last Post: 02-09-2009, 02:04 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