Macro for highlighting dates in red and orange

[sunilwadhwa]

Hi,

Can someone please help me edit an existing macro? Need 3 changes –

1) The macro creates a new workbook and also copies the hidden rows from the original file. I don’t need the hidden rows on the new workbook
2) On the new workbook sheet1, sheet2 & sheet3 are blank and need to be deleted.
3) On the new workbook “decision date” column any date before today’s date should be highlighted in red and anything = > today’s date should be highlighted in orange

Hope someone can help me out with this please!!!

Have attached sample data and vba code pasted below –

Please Login or Register  to view this content.

[venkat1926]

“today” is volatile. Today “today” means 20 July. Tomorrow “today” means 21 July
So you need automation that on each day it should color correctly.
So I used an event handler which runs a standard macro , that is everyday when you open the file coloring will be done correctly

The following macro may be parked in a standard module in vb editor.

'On the new workbook “decision date” column any date before today’s
'date should be highlighted in red and anything = > today’s
'date should be highlighted in orange


'ORANGE IS NOT A PRIMARY COLOR. SO I USED YELLOW. IF YOU INSIST ON ORANGE FIRND OUT
'RGB NUMBER FOR ORANGE
‘the above 2 paras are only comments. May be removed later.

Please Login or Register  to view this content.

Click Developer ribbon and click vbeditor (extreme left). If necessary click control + R
You get the project window on the left with list of open workbooks names
Go to the relevant workbook and right click “thisworkbook” and click view code
In the window that comes up copy this event handler

Please Login or Register  to view this content.

Save the file.
I am sure you know how to open a file which is macro enabled.
Open and see what happens

For test purposes
Highlight complete column U and
Click Home ribbon and click “fill color” and click “nofill”

The colors will be removed from cells in column U
Save the file and close
Again open what happens!!
I am also sending the file now called "sunil.xlsm" as attachment

remove sheet2 and sheet 3 manually. once removed it will not come in the macro. that is why manual removal woulld be bett


THERE IS NOT NEED FOR A NEW WORKBOO. IF YOU NEED YOU CAN SAVE THE ORIGINAL FILE (BEFORE ADDSING MACROS) SOME WHERE ELSE.

[sunilwadhwa]

Venkat,

Thanks for your macro. I still need the macro to create a new workbook along with new worksheets just like the old macro does. Please let me know if that's possible for you to help with. The old macro creates new worksheets based on "Ind Name" on column "x".

If you look at the old macro it only picks specific dates (today + next 7 days). If today is friday, all dates before today need to be in red and the dates ONLY TILL next friday need to be in orange.

You may want to run the old macro to see how it works. The old macro can be found on my original post.

Once again, thanks for all your help!!!

Regards,
Sunil

[sunilwadhwa]

Hi,

Can someone please help me with this macro....

Regards,
Sunil Wadhwa