Highlight Cells which are After the Date in Cell with Red Once Its Past the End Date

**Fredbugatti** · 09-17-2014, 11:38 AM

I need my Spreadsheet to be highlighted Red J under Status once Date on column I once it is past the end date.
Since I run events from From Start Date to End Dates and would need to know once event End date is reached, it will automatically turn red and i can delete off spreadsheet.

I would like all Rows after the end dates to be Red saying Ended and would like current dates that are before end date to be green and say Active.

I have attached a Spreadsheet for your reference.

Appreciate any help!!!

**judgeh59** · 09-17-2014, 11:47 AM

Give this a go

**Fredbugatti** · 09-17-2014, 11:52 AM

This is perfect!!! I would like the Whole Column down to have that formatting, How will I do that?

**judgeh59** · 09-17-2014, 11:53 AM

It will--- as soon as you enter a date in the Event End (I think that's the name) it will fill in the "Ended", "Active" and then the conditional formatting will take over...

**FDibbins** · 09-17-2014, 11:54 AM

I would avoid using CF om entire columns/rows, it can become very resource-intensive and start to slow things down. Rather, apply it to just what you need

**Fredbugatti** · 09-17-2014, 11:55 AM

Thanks for the info, Last question How does excel identify what is todays date that it knows if its past the date?

**FDibbins** · 09-17-2014, 11:57 AM

by using =TODAY()

Also, excess use of this will tend to slow things down (it is volatile, and recalcs with every workbook change). We often just put it its own cell (once) and then just reference it

**judgeh59** · 09-17-2014, 11:59 AM

In the last column I put a formula that compares TODAY() (a built function that returns todays date) from Event End date.

Dates in XL are just numbers formatted as Dates. Today's date, in numbers, is the number of days since 1/1/1900....weird, yes, but it makes doing integer math on dates really easy...

HTH

NOTE: Ford is very correct on the resource HOG of CF....

**Fredbugatti** · 09-17-2014, 12:00 PM

That is pretty amazing! So I not need to change todays date every day, it does it automatically correct?
Sorry for the nooby questions, I am not as advanced as you guys

**judgeh59** · 09-17-2014, 12:04 PM

that is correct, you don't have to change the date......

and to Ford suggestion....you can choose a cell anywhere (even on a hidden worksheet) and then reference that in the formula

**judgeh59** · 09-17-2014, 12:08 PM

I'm glad Ford and I could lend a hand

If this has been completed to your satisfaction, please set the thread to Solved...If you feel we deserve an increase in our reputation, feel free to click on the star in the lower left of the thread. Remember, someday when you help somebody they may add to your rep points, and it could be us...just a thought...

**Fredbugatti** · 09-17-2014, 12:35 PM

Is there any way to reference them after deletion to a different worksheet in the same excel sheet with a click of a button ?

**judgeh59** · 09-17-2014, 12:38 PM

If I understand what you are asking is

If I click a button (run a macro) can the marco move the data (that row) to the next line in a different worksheet in the same workbook.

if that is correct, then, yes, that can be done using a macro....

if you need assistance with that I would recomment starting a new thread according to forum rules...

**Fredbugatti** · 09-17-2014, 12:50 PM

Why is one row not all green, it is happening alot... Any fix to this issue?
Here is an image of example Error.jpg

Please help

**judgeh59** · 09-17-2014, 01:03 PM

I can't duplicate it on the file I sent you....can you upload the current file?

**Fredbugatti** · 09-17-2014, 02:08 PM

Sure, Here below is a Dropbox Link of the File
https://www.dropbox.com/sh/jnfvcvvuq...W23mp5IAa?dl=0

**Fredbugatti** · 09-17-2014, 02:18 PM

Sure, Here below is a Dropbox Link of the File
https://www.dropbox.com/sh/jnfvcvvuq...W23mp5IAa?dl=0

**judgeh59** · 09-17-2014, 02:33 PM

I can't get to that because of system restrictions....can you upload it?...

**Fredbugatti** · 09-17-2014, 02:56 PM

Sure, Attached is file.

**judgeh59** · 09-17-2014, 03:04 PM

ahhhh.....A file is worth a thousand words or posts in this case.....

in the conditional formatting the Applies to are got all messed up...

in the applies to field for both CF's it should be =$A:$J

that should clean it up....

**Fredbugatti** · 09-17-2014, 04:06 PM

Where do i insert those code in since it is a really long code?
Would you please reupload correct format?

**judgeh59** · 09-17-2014, 04:10 PM

here you go

**Fredbugatti** · 09-17-2014, 04:25 PM

Works Perfect, Thanks so much for the help!!

**judgeh59** · 09-17-2014, 04:54 PM

If this has been completed to your satisfaction, please set the thread to Solved...If you feel we deserve an increase in our reputation, feel free to click on the star in the lower left of the thread. Remember, someday when you help somebody they may add to your rep points, and it could be us...just a thought...

**judgeh59** · 09-18-2014, 12:22 PM

Glad we could help...thanks for the rep point...

**Fredbugatti** · 09-19-2014, 02:05 AM

Hey, I am still having the same issue with some cells appearing white?
Please help me out...

**Fredbugatti** · 09-19-2014, 02:09 AM

Attached is the Spreadsheet....

**Fredbugatti** · 09-19-2014, 02:14 AM

Never mind i just resolved it...

**Fredbugatti** · 09-29-2014, 12:15 AM

How would i continue on formatting for selected rows since some are not formatting after a specific column?
How will I continue on formatting cells?

**FDibbins** · 09-29-2014, 12:40 AM

Adjust the range in "applies to" to include all the areas you want to CF

**Fredbugatti** · 09-29-2014, 10:44 AM

Thanks!
Would i also be able have deleted cells from Sheet 1 Transferred to Sheet 2 once deleted ?

**Fredbugatti** · 09-29-2014, 11:54 PM

I am still having issues where some of my rows are showing white...... Like attached would you please fix the issue.
I would also like to have all cells that are deleted to automatically go to Sheet 2....HOLD ORDER ALL EVENTS-MINES TEST.xlsx

Thanks!!

**judgeh59** · 09-30-2014, 12:26 PM

I work on the CF and got it working the way you want...

Ford is correct on adjusting the area you want the CF to effect...as far as the request to have stuff deleted after you move it...that's a new post (according to forum rules)...

NOTE: when you are in the CF dialog box and you click on cells/rows/columns it is possible to accidentally change the applies formula, so, be gentle....

Highlight Cells which are After the Date in Cell with Red Once Its Past the End Date

LinkBack

Thread Tools

Rate This Thread

Display

Highlight Cells which are After the Date in Cell with Red Once Its Past the End Date

Re: Highlight Cells which are After the Date in Cell with Red Once Its Past the End Date

Re: Highlight Cells which are After the Date in Cell with Red Once Its Past the End Date

Re: Highlight Cells which are After the Date in Cell with Red Once Its Past the End Date

Re: Highlight Cells which are After the Date in Cell with Red Once Its Past the End Date

Re: Highlight Cells which are After the Date in Cell with Red Once Its Past the End Date

Re: Highlight Cells which are After the Date in Cell with Red Once Its Past the End Date

Re: Highlight Cells which are After the Date in Cell with Red Once Its Past the End Date

Re: Highlight Cells which are After the Date in Cell with Red Once Its Past the End Date

Re: Highlight Cells which are After the Date in Cell with Red Once Its Past the End Date

Re: Highlight Cells which are After the Date in Cell with Red Once Its Past the End Date

Re: Highlight Cells which are After the Date in Cell with Red Once Its Past the End Date

Re: Highlight Cells which are After the Date in Cell with Red Once Its Past the End Date

Re: Highlight Cells which are After the Date in Cell with Red Once Its Past the End Date

Re: Highlight Cells which are After the Date in Cell with Red Once Its Past the End Date

Re: Highlight Cells which are After the Date in Cell with Red Once Its Past the End Date

Re: Highlight Cells which are After the Date in Cell with Red Once Its Past the End Date

Re: Highlight Cells which are After the Date in Cell with Red Once Its Past the End Date

Re: Highlight Cells which are After the Date in Cell with Red Once Its Past the End Date

Re: Highlight Cells which are After the Date in Cell with Red Once Its Past the End Date

Re: Highlight Cells which are After the Date in Cell with Red Once Its Past the End Date

Re: Highlight Cells which are After the Date in Cell with Red Once Its Past the End Date

Re: Highlight Cells which are After the Date in Cell with Red Once Its Past the End Date

Re: Highlight Cells which are After the Date in Cell with Red Once Its Past the End Date

Re: Highlight Cells which are After the Date in Cell with Red Once Its Past the End Date

Re: Highlight Cells which are After the Date in Cell with Red Once Its Past the End Date

Re: Highlight Cells which are After the Date in Cell with Red Once Its Past the End Date

Re: Highlight Cells which are After the Date in Cell with Red Once Its Past the End Date

Re: Highlight Cells which are After the Date in Cell with Red Once Its Past the End Date

Re: Highlight Cells which are After the Date in Cell with Red Once Its Past the End Date

Re: Highlight Cells which are After the Date in Cell with Red Once Its Past the End Date

Re: Highlight Cells which are After the Date in Cell with Red Once Its Past the End Date

Re: Highlight Cells which are After the Date in Cell with Red Once Its Past the End Date

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Highlight Cells which are After the Date in Cell with Red Once Date is reached

[SOLVED] Conditional Formatting: Highlight a cell containing a date after 1 year past that date!

Highlight & count tasks past due date

how can i highlight something when it goes past a certain date

[SOLVED] HIGHLIGHT A DATE 10 DAYS BEFORE IT IS PAST DUE

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions