+ Reply to Thread
Results 1 to 3 of 3

Help With IFAND Formula on Conditional Formatting Range

  1. #1
    Registered User
    Join Date
    06-17-2014
    Location
    UK
    MS-Off Ver
    2013
    Posts
    49

    Help With IFAND Formula on Conditional Formatting Range

    Trying to get conditional formatting to highlight a range based on dates at the top of a column and whether this date is within specific start and end dates of various projects.

    Have various projects with start and end dates for example:

    ​Project Number Project Name Start Date End Date Apr-15 May-15 Jun-15 Jul-15
    Example 1 Project 1 01/05/2015 30/06/2015
    Example 2 Project 2 01/06/2015 30/06/2015


    What I need is a conditional formula to highlight cells which are not in the range of the project.
    So on Example 1 the cell in the columns Apr-15 & Jul-15 will be grayed out as the start date is 01/05/2015 to 30/06/2015.


    Currently have the following IFAND formula in Excel which works:

    =IF(AND(BI$4>=$E$6,BI$4<=$F$6),TRUE, FALSE)

    Where BI$4 is the date on top of a column, E is the start date of the project and F is the end date of the project.

    But when I try to put this in Conditional Formatting rule as:

    =IF(AND(BI$4>=$E$6,BI$4<=$F$6))

    I'm getting the formula you typed contains an error.

    Any ideas as to how I can rephrase the formula to get this rule to work across a range?

  2. #2
    Valued Forum Contributor tony h's Avatar
    Join Date
    03-14-2005
    Location
    England: London and Lincolnshire
    Posts
    1,187

    Re: Help With IFAND Formula on Conditional Formatting Range

    In the formula value you want something that returns True or False so you don't need the If

    So change =IF(AND(BI$4>=$E$6,BI$4<=$F$6))
    to =AND(BI$4>=$E$6,BI$4<=$F$6)
    or to =IF(AND(BI$4>=$E$6,BI$4<=$F$6),true,false)

    Just check in the conditional formatting for both the formula and the target that you are using $ in the right places. If you are simply copying and pasting the formatting will copy the conditional formatting


    click on the * Add Reputation if this was useful or entertaining.

  3. #3
    Registered User
    Join Date
    06-17-2014
    Location
    UK
    MS-Off Ver
    2013
    Posts
    49

    Thumbs up Re: Help With IFAND Formula on Conditional Formatting Range

    Thanks, that worked

+ 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. [SOLVED] Conditional formatting formula range update
    By sa02000 in forum Excel General
    Replies: 4
    Last Post: 03-12-2014, 07:01 PM
  2. I need some help with an IFAnd formula; real confussed.
    By susiesc in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-25-2013, 11:19 AM
  3. [SOLVED] conditional formatting formula to search a range of cells
    By drfarmkid in forum Excel General
    Replies: 2
    Last Post: 06-19-2012, 03:25 PM
  4. Question on IFAND formula in Excel - & is it the best method
    By galvinpaddy in forum Excel General
    Replies: 2
    Last Post: 12-14-2011, 11:47 AM
  5. Conditional formatting formula -Data range?
    By elfiky in forum Excel General
    Replies: 2
    Last Post: 09-27-2011, 02:44 AM
  6. highlight comparison using conditional formatting with formula in range
    By tjc0ol in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-29-2011, 08:48 AM
  7. Gnatt chart - Need help with Ifand formula
    By Kristina1976 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-10-2011, 02:05 PM
  8. Conditional formatting with formula referencing range in different cell
    By pethaa1791 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 10-21-2008, 03:54 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