+ Reply to Thread
Results 1 to 10 of 10

Auto Colour cells if a DATE is in adjacent cell.

  1. #1
    Registered User
    Join Date
    05-03-2012
    Location
    Liverpool
    MS-Off Ver
    Excel 2003
    Posts
    16

    Auto Colour cells if a DATE is in adjacent cell.

    Hey guys,

    I have a quick question that I'm sure someone could solve in a matter of seconds.

    I have a stock spreadsheet.

    Columns and rows from A1:E107 full of product info. These are my stock lines.

    All I'm looking to do is, if a dispatched date is entered into column F, next to the stock line, The line of stock will highlight in green.

    That's all.

    I think it's a conditional format of some type but I can't seem to get it to work.

    Any Ideas?

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    19,803

    Re: Auto Colour cells if a DATE is in adjacent cell.

    Highlight all the data, i.e. A2:F107, assuming you have row 1 for headers. Then click on Conditional Formatting and choose Formula Is rather than Cell Value Is, and then enter this formula in the box:

    =$F2<>""

    Then click the Format button, then the Patterns tab and choose green. Then OK your way out, and Excel will adjust the cell references as appropriate. Note, if you don't have a header row and highlight A1:F107, then the formula should be =$F1<>"".

    Hope this helps.

    Pete

  3. #3
    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: Auto Colour cells if a DATE is in adjacent cell.

    Maybe?

    In Conditional Formatting rules.

    =$f1>0

    Range A1:E107
    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.

  4. #4
    Registered User
    Join Date
    05-03-2012
    Location
    Liverpool
    MS-Off Ver
    Excel 2003
    Posts
    16

    Re: Auto Colour cells if a DATE is in adjacent cell.

    Worked PERFECTLY!!!

    I can't thank you enough!!!

    Just one more thing though if you would be so kind!

    column E is full of weights for my products... Is there any way I can have one cell say H3 keep a running total of the highlighted weights?

    So If a row is highlighted, then cell H3 would increase by that amount of weight in column E?

  5. #5
    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: Auto Colour cells if a DATE is in adjacent cell.

    Suggestion 1

    In H1 and copy down

    =IF(F1<>"",E1,"")

    Or

    In H1

    =IF(F1<>"",E1,"")

    In H2 and copy down.

    =IF(F2<>"",E2+MAX($H$1:H1),"")

  6. #6
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    19,803

    Re: Auto Colour cells if a DATE is in adjacent cell.

    I think you want one cell to contain the total of the weights on rows that are highlighted, so you can put this formula in H3:

    =SUMIF(F2:F107,"<>",E2:E107)

    Hope this helps.

    Pete

  7. #7
    Registered User
    Join Date
    05-03-2012
    Location
    Liverpool
    MS-Off Ver
    Excel 2003
    Posts
    16

    Re: Auto Colour cells if a DATE is in adjacent cell.

    Thanks Pete_UK,

    My weights are in column F2:f107

    In your forumula, how does E2:E107 fit into it?

  8. #8
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    19,803

    Re: Auto Colour cells if a DATE is in adjacent cell.

    Read through your previous posts. You have said that the dates are in column F and that the weights are in column E, so that is what my formula has assumed. If these have now changed then you need to adjust the formula to suit.

    Hope this helps.

    Pete

  9. #9
    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: Auto Colour cells if a DATE is in adjacent cell.

    It would was also very helpful and kind to me (not wasting my time-trying to help you), if you had told us that i am in your IGNORE list....

  10. #10
    Registered User
    Join Date
    05-03-2012
    Location
    Liverpool
    MS-Off Ver
    Excel 2003
    Posts
    16

    Re: Auto Colour cells if a DATE is in adjacent cell.

    @Fotis1991

    I thanked you for your first contribution which helped me.

    Your second, I was trying and tweaking. Then the other one came along which worked. It wasn't that your advice was ignored, I was simply in the process of testing it when I replied to Pete.

    If I seemed rude, I do apologize! Your input was appretiated!

+ 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