+ Reply to Thread
Results 1 to 6 of 6

conditional formatting formula, to highlight cells in column b, based on dates in column a

  1. #1
    Registered User
    Join Date
    09-21-2012
    Location
    Vancouver, BC
    MS-Off Ver
    Excel 2007
    Posts
    3

    conditional formatting formula, to highlight cells in column b, based on dates in column a

    I'm trying to create a conditional format that highlight cells in column e, when the date in column a, falls within a one year period.

    I created a sumif function to add those amounts:

    =SUMIFS(E14:E78,A14:A78,">"&D8,A14:A78,"<="&D8+365)

    The start date is in cell D8.

    In addition to summing the relevant cells though, I wanted to highlight them as well. However, I've tried inputing variations of the above formula into the worksheet and it's not really working out.

    Does anybody have any suggestions?

    Thank you.

  2. #2
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: conditional formatting formula, to highlight cells in column b, based on dates in colu

    Hello and welcome to the forum.

    Please upload a sample workbook.. thanks.

    Regards,
    DILIPandey

    <click on below 'star' if this helps>
    DILIPandey, Excel rMVP
    +919810929744 (India), +971528225509 (Dubai), [email protected]

  3. #3
    Forum Expert Kevin UK's Avatar
    Join Date
    12-07-2010
    Location
    Radstock, Somerset
    MS-Off Ver
    365
    Posts
    1,922

    Re: conditional formatting formula, to highlight cells in column b, based on dates in colu

    You could use the AND function for the conditional formatting between the dates.

  4. #4
    Forum Contributor
    Join Date
    01-17-2012
    Location
    Canada
    MS-Off Ver
    Excel 365
    Posts
    221

    Re: conditional formatting formula, to highlight cells in column b, based on dates in colu

    cond_format3.xlsx

    hey there, try using this. I find it helpful to test the formula I intend to use first before copying it into the conditional format manager (see data in columns H:L)

    To apply, copy the formula in H14 and then highlight the range you want to conditionally format > select use a formula > paste and edit the format however you like.

    Hope this helps!
    Regards,

    jeversf

  5. #5
    Registered User
    Join Date
    09-21-2012
    Location
    Vancouver, BC
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: conditional formatting formula, to highlight cells in column b, based on dates in colu

    Here's the workbook I'm trying to do this in.Book1.xlsx

  6. #6
    Registered User
    Join Date
    09-21-2012
    Location
    Vancouver, BC
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: conditional formatting formula, to highlight cells in column b, based on dates in colu

    Thanks! That seems to have done the trick!

+ 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