+ Reply to Thread
Results 1 to 9 of 9

Conditional Formatting with dates

  1. #1
    Registered User
    Join Date
    02-12-2015
    Location
    Channel Islands
    MS-Off Ver
    2010
    Posts
    5

    Conditional Formatting with dates

    I have a date in Column H (date 3) that I want to turn either red if it is after the date in column G (date 2) or green if it is on or before. This I can do without a problem. The problem that I have is then pasting that formatting down into other cells and is picks up the original row in column G that I started with! Can anyone help!


    Date 1 Date 2 Date 3
    09/02/15 13/02/15 14/02/15

    ** date two is a formula of Date 1 +4days

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,463

    Re: Conditional Formatting with dates

    Does it come from absolute reference?

    Assuming your CF in H2:

    =H2>$G$2

    adjust to

    =H2>$G2

    then copy /paste format down
    Quang PT

  3. #3
    Registered User
    Join Date
    05-26-2014
    Posts
    9

    Re: Conditional Formatting with dates

    This is because of absolute reference, hope the following helps,

    $A$1 - Both the column and row reference are fixed. Neither will be incremented or changed during a copy or fill operation.
    $A1 - Only the column reference is fixed. It will not change during a fill or copy, but the row will change.
    A$1 - Only the row reference is fixed. It will not change during a fill or copy, but the column will change.

    Thanks

  4. #4
    Registered User
    Join Date
    02-12-2015
    Location
    Channel Islands
    MS-Off Ver
    2010
    Posts
    5

    Re: Conditional Formatting with dates

    Thank you for your quick response it has worked!!! I have been bashing my head against a brick wall over that one for the last week!

  5. #5
    Registered User
    Join Date
    02-12-2015
    Location
    Channel Islands
    MS-Off Ver
    2010
    Posts
    5

    Re: Conditional Formatting with dates

    Actually it did work! for H2 & 3 but when I copied it down further it reverted back to picking up the date from G2

  6. #6
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,463

    Re: Conditional Formatting with dates

    Send a dummy file to check what happen

  7. #7
    Registered User
    Join Date
    02-12-2015
    Location
    Channel Islands
    MS-Off Ver
    2010
    Posts
    5

    Re: Conditional Formatting with dates

    I think I have uploaded it ok!
    Attached Files Attached Files

  8. #8
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,463

    Re: Conditional Formatting with dates

    Set the whole range in box "Applies to": $H$2:$H$4, instead of $H$2 only

  9. #9
    Registered User
    Join Date
    02-12-2015
    Location
    Channel Islands
    MS-Off Ver
    2010
    Posts
    5

    Red face Re: Conditional Formatting with dates

    Thank you I see where I went wrong now... you help is apprecated

+ 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. Conditional formatting using dates and using dates without years
    By dcef79 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-15-2014, 05:35 AM
  2. Replies: 11
    Last Post: 05-04-2014, 08:28 PM
  3. Conditional Formatting with approaching due dates and completed dates
    By rogernation in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-13-2013, 04:12 PM
  4. Replies: 4
    Last Post: 08-29-2013, 11:23 AM
  5. [SOLVED] Conditional Formatting of: dates within 1 month and dates before today.
    By Luke Smith in forum Excel General
    Replies: 5
    Last Post: 06-18-2013, 07:29 AM

Tags for this Thread

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