Check date throughout work sheet and highlight row

[SarahPintal]

hi guys,

I have something it would be so handy if I could get some assistance with.

I have a document that has many work sheets,

on each work sheet there are 2 columns as per below:

'Reported Date+' & 'Last Resolved Date'


What would be handy if i had a macro that would check all work sheets (expect the one titled 'Historic' )and look at\calculate 2 columns with the title 'Reported Date+' & 'Last Resolved Date' .

If 10 days or greater has elapsed (using the 'Reported Date+' entry as the start date) and there is not a time entry under 'Last Resolved Date' for the line time, then the entire row can be highlighted RED.


I.e


Reported Date+ Last Resolved Date
23/11/09 10:30

The macro would count 10 days from 23/11/09 10:30 and if there isnt a time under 'Last Resolved Date' then the row would be highlighted in RED.

FYI: I would much prefer the macro to look for the column titles than the column letters as the columns may be shuffled around a tad at a later date (no pun intended :P ).


Thanks guys !

[SarahPintal]

Oh, i forgot to say,

At the moment there are only 4 worksheets though soon there will be 5,6,7,8,40,50,.. anywhere up to 80, so need it to be in a macro instead of a conditional static formate.

thanks guys

[teylyn]

Why a macro? Conditional formatting will do the job nicely. You just need to set it up correctly. Maybe upload a small data sample so we can help you get it just right. to upload a file, click "go advanced" below and then the paper clip icon.

As a general approach, if the reported date is in column A with A1 being a header, and Last Resolved date in column B, you could

- highlight all rows and columns with data
- select Format - conditional formatting
- select formula is and enter this

=and(isblank($b1),today()>$A1+10)

select a format and hit OK

The conditional formatting can be copied and pasted with Paste Special - Formats (or with the format painter).

hth

[SarahPintal]

Thanks for the tip

The reason I think a macro is the best approach as I currently have a macro that imports data into a worksheet, then i click a macro button that distributes the data from worksheet1 into either a) existing worksheets or b)creates a new worksheet if the worksheet does not exist.

Ie:
Current work sheets; John,Tony.

New data on worksheet 1;
John had tea today
Adam had corn today
Luke had an apple
Tony couldnt afford an apple to had a grape

Action: press macro distribute button:
Result: Adam and Luke worksheets are created & the new data is added to Adam and Luke... and the new data is also copied respectivly to John and Tony without creating a new worksheet.

I want to be able to go straight to these worksheets and for the rows to be highlighted to alert if the date is blank \ exceed the 10 day threshold.


i guess for the existing worksheets a conditional format string will work great, though as it wont be created dynamically with a new worksheet manual work will be required.

For me this is okay, though I wont be the only one using it i suspect

anyhelp would be so much appreciated

[teylyn]

Well, in this case you could have a worksheet that serves as a template for any new worksheets. The template would have the conditional formatting already applied and your macro would not create a new worksheet, but rather a copy of the template and then fill in the data ....?

[SarahPintal]

Interesting, interesting

That is a little beyond me! hehe, i know, i know, your better at this than me

I have attached my document with no data in it, it has all my code though.

I am not sure how to create a template and apply it to my document.

[teylyn]

I'm no VBA wiz, quite the contrary, thus my preference for a formulaic approach.
If you copy your Historic sheet and call it Template, then apply the conditional formatting to the Template sheet, you could probably use it in your workflow.

I see in your code the sub

Please Login or Register  to view this content.

Some of these lines could probably be changed to copy the template sheet instead of creating a new sheet, but the correct syntax momentarily escapes me. Something like

Please Login or Register  to view this content.

whoever wrote the code for your workbook will probably be able to figure it out much better than I could.

I won't be good for solid VBA advice for a while yet, but maybe this'll give you enough of a start.

hopefully ...

[SarahPintal]

Thanks,

I was able to do it myself in the end! took a lil while though... I used a modification of your conditional format.

I recorded a macro, made 1 cell adjustments with conditional format, then stoped the macro.

I then called the sub in 2 other subs. Once when my code distributes the new rows to the respective work sheets and again when it copies the rows to the historic worksheet.

Thanks for your help, without you I wouldnt have been able to do it. Your thoughts + a little bit of sweat equaled magic