Highlight and unhighlight cells

[Jay-1.1]

Hi,

I have the below macro that highlights any collection date that is 5 days old. I have two fields one called collection (a) and one called delivered (b). when an item is collected a date gets entered, if the item that was collected isn't delivered within 5 days the field highlights. The problem that I'm having is that if the item is delivered after I run the macro the cells stay highlighted! If I clear the fill and re-run the macro if works fine.

Any suggestions would be very welcome?

Please Login or Register  to view this content.

[stnkynts]

I believe that "wdNoHighlight" is the wrong sytax. Try "xlNone" instead. Should clear out the highligh if a value in column A & B is True. Which brings up the other question.... Will those columns actually have a "True" value?

[xladept]

Hi Jay,

Maybe you should make it a Worksheet_Change event - then it would run when either critical field was accessed!

Please Login or Register  to view this content.

[Jay-1.1]

I believe that "wdNoHighlight" is the wrong sytax. Try "xlNone" instead. Should clear out the highligh if a value in column A & B is True. Which brings up the other question.... Will those columns actually have a "True" value?

No they will not. the two cells are formatted to date format and will only contain dates.

The first if statement seems to work fine though, I just can't get the second if statement to work correctly. It has been a long time since I last created a macro.

I did try xlNone but it didn't work. I know the true and false values are wrong, i just couldn't think of anything else to put.

macro.png

Row 5 is highlighted because there is no date in the B5 cell. if I add a date to the b5 cell and run the macro the cells stay highlighted. I want the highlighted cells to un-highlight if I add a date to it.

Thanks for your help so far.

[stnkynts]

I figured you did not mean to put "True" and "False".

Try:

Please Login or Register  to view this content.

[Jay-1.1]

Thank-you! That is exactly what I wanted it to do

[xladept]

Hi Jay,

Now that it's working, you should try the event procedure - do you need instructions for that?

[Jay-1.1]

Yes, please.

[Jay-1.1]

I figured you did not mean to put "True" and "False".

Try:

Please Login or Register  to view this content.

Hi,

thank-you for your help so far!

i have noticed that if i add a date that is more than 5 days away into an empty cell in field A the row will not highlight, how can I get it to highlight once i add a new date that is more than 5 days old to that empty cell?

[Jay-1.1]

Hi,

thank-you for your help so far!

i have noticed that if i add a date that is more than 5 days away into an empty cell in field A the row will not highlight, how can I get it to highlight once i add a new date that is more than 5 days old to that empty cell?

Sorry really didn't explain myself properly.

I meant that if I change a date in row A to a date that is not 5 days away then the rows stay highlighted.

for example: if the date in the collected row is 10/03/12 but then, for whatever reason, I have to change the date to the 20/03/13 the row will stay highlighted even though it shouldn't because it's got a date that isn't more than 5 days away.

also, if a cell is empty in row a then it will highlight, but if I add a date that isn't more than 5 days away to that cell it still stays highlighted.

I have been messing around with it for awhile now but I just can't get it working.

Any ideas?

[xladept]

Hi Jay,

The event procedure should take care of any entries in "A" or "B" columns. To use the event procedure, you need to click on the sheet where the data is: Copy the event code -In the VBE click on the sheet, it will bring up the sheet module - Type "Option Explicit" and the paste the code you copied under it - Then you're set to go

[Jay-1.1]

Hi Jay,

The event procedure should take care of any entries in "A" or "B" columns. To use the event procedure, you need to click on the sheet where the data is: Copy the event code -In the VBE click on the sheet, it will bring up the sheet module - Type "Option Explicit" and the paste the code you copied under it - Then you're set to go

Hi,

I added the event procedure but it didn't work.

Any ideas?

[xladept]

You have to put it in the sheet that's Sheet1,Sheet2 or Sheet3 - you've got it in Module1 - so move it to Sheet1 or whichever Sheet you're using. Then it'll work and you'll be pleased

BTW don't forget to write "Option Explicit" at the top of the Sheet module!

[Jay-1.1]

You have to put it in the sheet that's Sheet1,Sheet2 or Sheet3 - you've got it in Module1 - so move it to Sheet1 or whichever Sheet you're using. Then it'll work and you'll be pleased

BTW don't forget to write "Option Explicit" at the top of the Sheet module!

not.png

I did try that at first but it didn't work and that's why i tired it in module. I did try to remove the uploaded photo after I realised that it would never work but it wouldn't let me.

field a.png

[xladept]

Can you post a sample sheet with non-sensitive data - if you'll do that, I'll set it up for you and send it back when it's working?

(To attach a file => go to advanced and click on the paper clip icon.)

[Jay-1.1]

The file is attached.

Thanks

[xladept]

Hi Jay,

Here it is:Jay.xlsm

[Jay-1.1]

Hi Jay,

Here it is:Attachment 223366

Thanks for all you help so far.

It works but not quite how I want it to. If I change a date in the collected column to one that isn't more than 5 days old the the rows stay highlighted. any ideas on how I can fix this?

[xladept]

That's done in the ProcessData program - what should be happening with a new entry and no completion date??

[Jay-1.1]

That's done in the ProcessData program - what should be happening with a new entry and no completion date??

The problem occurs when I change the collection date! If the collection date isn't 5 days old then I don't want it to highlight because it can take 3 or 4 days to be returned. Also, when I've added the macro to a filtered sheet is doesn't seem to work the same. any help on this would be very much appreciated because I can't work it out.

[Jay-1.1]

cheers for all your help xladept!!!!!!!!!!!!!

I will figure it out eventually.