Format cells based on Date

[wayneryeven]

Hey all
I have a simple worksheet with a Date in Column A. All i want to do is loop through each row until the end, and IF the date is WITHIN CURRENT DATE+3 then change the font colour to Red.

I have a formula in Column B that compares A2 to Current Date+3 and enters in a value. However id like to flag the rows a different colour, and for the formatting only to apply to the number of ACTIVE rows in A.

I trust this makes sense, im sure it is achievable so all comments and suggestions welcomed!

 

[HarleyQuinn]

I'd have a look at Conditional Formatting.

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[GlennUK]

As HarleyQuinn says, Conditional Formatting. A condition of =A1 < (TODAY()+3)
might do the trick.

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[wayneryeven]

I dont want to go to Conditional formatting due to the sheer number of rows and the impact on performance that Conditional formatting will have?

 

[HarleyQuinn]

I think looping the rows will have a much greater performance impact than the conditional formatting will ever have (and has had in my testing of both solutions).

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[combo]

For a single use it can be done with advanced filter and manual formatting, after selection hidden cells are not formatted.

combo

 

[AnotherHiggins]

-> sheer number of rows
How many are we talking about?

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[techie131]

I want format the excel cells for dates in such a way. If an user puts in date 030909 than it will automatically change to 03/09/09. I have tried to format the cell using custom setting to dd/mm/yy but when I enter 030909 than it gives me a bunch of # signs indicating invalid date. Does anyone know how to format this way.

 

[SkipVought]

Hi,

You need to read and UNDERSTAND this faq...

faq68-5827

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gavona]

wayner1980: I would try conditional formatting but if you want to "loop through..... " then you should be posting in forum707.
Looping is an inefficient solution. Try filtering and formatting the filtered cells only.

Without vba or conditional formatting I would:
create a cell Today()+3 as an Advanced Filter criteria

You could also apply an AutoFilter to column B ("I have a formula in Column B that compares A2 to Current Date+3 and enters in a value).

Either way having filtered your data to only show the rows you want to be red: Select visible cells only and format them.

You may need to first remove the existing formatting.

Having told you how without VBA (macros) you can of course switch the macro recorder on andf record yourself doing the above....


Gavin

 

[PeterMoran]

Hi huotivgmail,

If you re-enter your query as a separate thread, I may be able to help you.

It is not appropriate for obvious reasons to have two subjects in the one thread, regardless of how similar they may appear to be.

Good Luck!
Peter Moran