macro for creating colored blinking colors in rows

This is a sheet that I use to track cabins and rv’s that I rent. People call, make reservations, and it’s all tracked on this SHRUNK DOWN SHEET. This sheet is quite a large sheet. Has been made smaller so I can upload it here.

Basically what I’m looking for, is a macro/code to do the following.

On sheet named “cabin reservation”. Under the “D” column, is codes named “In, out, NI, or canceled” these words trigger some conditional formatting colors.
In the “D” column I would like for the VBA to Look to see if it says “in” and if it does, look at column “K” that corresponds to that customers check out date. If the check out date is today, the code will look at the computer clock to see if it is past 12 noon. And if it is past 12 noon, the code will make the entire row with check out date past noon flash multiple colors. This will continue to flash until the word “in” is changed to anything else.
This is to track if our customers forget to turn in the keys to the office. The office can see who is in and out.

Thank you in advance.


Jason
you can download a copy of the sheet at my dropbox here >>> https://www.dropbox.com/s/tesa1t9358...lors.xlsx?dl=0

Thank you very much for your time and effort. I appreciate it... i will take a look and try it out. Ill repost if it works for me.
Jason

Originally Posted by mehmetcik

I thought I had a solution for you but it is very slow.

This may be because you have so many conditional formats running.

I created three conditional formats each with a different fill color :-

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

This Macro:-

Please Login or Register  to view this content.

Hi there,

See if the attached version of your workbook does what you need.

It uses an extra helper column (C), a worksheet-level defined name "bHighlightText", and some code which toggles the value of "bHighlightText" between True and False at one-second intervals whenever the Cabin Reservation worksheet is active.

The values in the helper column will toggle between True and False whenever:

the value in Column E is "in", and
the value in Column L is today's date, and
the time displayed by the system clock is later than 12 noon

When the value in the helper column is True, conditional formatting highlights all of the data cells in that row, and this highlighting is removed whenever the value in the helper column is False.


The code used is as follows:

ThisWorkbook VBA CodeModule

Please Login or Register  to view this content.

Cabin Reservation Worksheet VBA CodeModule

Please Login or Register  to view this content.

Standard VBA CodeModule

Please Login or Register  to view this content.

Hope this helps - please let me know how you get on.

Regards,

Greg M

Hi again,

The Workbook_BeforeClose routine needs to be added to the ThisWorkbook VBA CodeModule as shown below:

Please Login or Register  to view this content.

Regards,

Greg M