+ Reply to Thread
Results 1 to 5 of 5

Excel 2007 : Conditional formatting: formatting various cells in row based on value of cell above

  1. #1
    Registered User
    Join Date
    01-05-2012
    Location
    Copenhagen, Denmark
    MS-Off Ver
    Excel 2007
    Posts
    2

    Conditional formatting: formatting various cells in row based on value of cell above

    Hi guys,

    Thanks for a great forum.
    I'm working on a spreadsheet in which I have a lot of "pairs" of rows like this:
    1 (Target): ValueA ValueB ValueC etc etc
    2 (Actual): ValueA ValueB ValueC etc etc

    I want a conditional formatting of the "Actual" row cells based on the value of the cells above in the "Target" row. E.g.
    If A$2 > A$1 --> Green
    If A$2 = A$1 --> Yellow
    If A$2 < A$1 --> Red

    I use $ to lock the row, but obmit it for the columns.
    PROBLEM: I want to copy the formatting to a LOT of cells while making sure the references change so that the formatting is always based on the cell directly above the cell in question. However, when I copy the formatting (e.g. with copy/paste special (formatting)) it keeps referencing back to the initial cell.
    How do I code a conditional formatting so that I can just copy/paste the formatting to a lot of other cells while ensuring that the reference cell changes to match the cell directly above the cell with the formatting?
    I'm sorry if it sounds confusing

    Thank you in advance,

    Christian

  2. #2
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Conditional formatting: formatting various cells in row based on value of cell ab

    Hi Christian and welcome to the forum.

    =A$2 > A$1>>> Copy>>Choose the range that you want>>Paste Special>>Click the Format Button>>OK.

    This works for me. For you????

    Hope to helps you.
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  3. #3
    Registered User
    Join Date
    01-05-2012
    Location
    Copenhagen, Denmark
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Conditional formatting: formatting various cells in row based on value of cell ab

    Fotis;

    Thanks for your quick reply!
    Unfortunately, when I do this and paste it into say E6 --> P6 the conditional formatting keeps referring back to E5 in all cells.
    What I want is the conditional formatting to refer to the cell above. E.g. E6 formats based on value in E5; F6 based on F5 etc. etc.
    With the copy/paste special, F6 formats based on value in E5; G6 based on E5, H6 based on E5 etc etc.

    Thanks

  4. #4
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Conditional formatting: formatting various cells in row based on value of cell ab

    ...I think that the best way to describe yoyr problem, is to upload a sample workbook.

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,265

    Re: Conditional formatting: formatting various cells in row based on value of cell ab

    Check in CF to see if the cell you are refering to is absoluted? If so, remove the $ and try the copy again

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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