Worksheet Activate Macro for highlighting rows based on the date which is end of month

[rehana402003]

Hello All,

I have excel 2013

There are 2 sheets

Sheet1, Sheet2

Data Starts from Row 12 on Sheet1 and Sheet2 (as shown in the sample attached)

I want a Worksheet_Activate Macro

Which should look up for dates in Col D and highlight the entire row based on the dates of the end of the month

If multiple entries are there for the end of the month then only the last row should be highlighted
Eg Sheet1- Row 19 and Row 20 are both 31-Jan-2018 then only Row 20 to be highlighted.

If the end of the Month is Friday or Saturday then it should highlight the day which is a day before
Eg Sheet2- Row 28 is 29-Mar-2018 which is a Thursday and it is highlighted and Row 35 is 28-Jun-2018 which is highlighted. Also Row 42 is 30-Aug-2018 which is a Thursday and it is highlighted

If the end of the month date is falling on a Friday or Saturday, the previous Thursday row to be highlighted

Hope I am clear

Any help would be appreciated
Thanks in advance
Rehana

[PCI]

If the end of the month date is falling on a Friday or Saturday, the previous Thursday row to be highlighted

If exists a record for 29 March 2018 (Thursday ) and another one for 31 March 2018 ( Saturday) we need to highlight Thursday record then the Saturday is not highlighted then

the entire row based on the dates of the end of the month is not highlighted

IS it OK ??

[rehana402003]

Hello PCI
Yes there will be no highlights for any days if the end of the month is Fridays or Saturdays (as these are none working days for us)
The entire row till Col N should be highligted
I missed one cell. Sorry for that

Thanks for your time

[PCI]

Yes there will be no highlights for any days if the end of the month is Fridays or Saturdays (as these are none working days for us)

The entire row till Col N should be highligted

or do you mean

The entire row till Col N should NOT be highligted

[rehana402003]

Dear PCI,
Sorry for the confusion
Following is my requirement
I will be doing entries everyday in both Sheet1 and Sheet2
which has already some conditional formatting applied for various conditions

When the entry is done for the last day of the month and the file opened the next time the row with the last date of the month should be highlighted. Friday and Saturday are non working days.
I want the row till column N to be highlighted indicating the entry of the last day of that particular month.

There may be more than one entry for the same date which will be the last day of the month.
I want the last entry to be highlighted. eg Sheet1 see Row 19 and 20. Row 20 is highlighted. same in Sheet2

If the last day of the month is a Friday or a Saturday (eg Mar 2018 and Aug 2018) then the row of previous day should be highlighted

I am enclosing another attachment for explanation purpuse
I have colored all the cells upto Col N manually

Hope the requirement is clear now

Thanks for your time once again.

[Phuocam]

Try this formula for Conditional Formatting:

=MONTH($D13)<>MONTH($D14)

[PCI]

In VBA in ThisWorbook folder put next code
See file attached to

Please Login or Register  to view this content.

[rehana402003]

Dear PCI
I have done some testing on the file attached herewith and noticed a strange behavior which I would like to share with you.
Workbook_Activate Macro does only one sheet with the correct output
The second sheet does not give the correct output
It can be Sheet1 or Sheet2 but not both the sheet shows the correct results

I have cleared the fill colors, saved and reopened the workbook and tested it several times

I noticed one thing that while opening the workbook if Sheet1 is active then the result would be correct on Sheet1 and if Sheet2 is active then it would give the correct result on Sheet2
However the module works perfect if run on each sheet individually.
I wanted to share my test with you.

Any thoughts?

Thanks for your time once again.

[rehana402003]

Hello Phuocam
Thanks for your solution too.

Regards

[PCI]

Yes there was a mistake, but it shown also a limit not specified: Clear filter to avoid missing rows
See next code

Please Login or Register  to view this content.

[rehana402003]

Thanks PCI
Reputation added
Post marked SOLVED

[PCI]

Good news
Have a great day