Highlight cells according to date today and next working date

[maryclaire_g]

Hi!
I am kindly requesting for a macro script that will run through all the sheets in the Excel file. The macro should be able to do the following:
1. Color the rows in yellow for a cell containing the date today and the format is ddmmmyy for most of the sheets. But there is 1 specific sheet that has this date format which should also be yellow: ddmmmyyyy
2. Color the rows in blue for a cell containing the next working date. Run in all sheets. Meaning, no Saturday or Sunday included.
3. For sheets that don't contain anything at all, put the word, "nil."
4. If there's a window where I can put the date to be excluded for a holiday (or weekends), that would be really helpful too.
*Please indicate in the script the sheet I should change the name for the sheet with the date format ddmmmyyyy
Example: CustomSheet = the sheet that I will change the name in the script to match the sheet name in the file with the custom date format
*The cell does not contain the date only inside. It has some other details too in the same cell. Conditional formatting is able to highlight it but it's time consuming because there are many sheets.
Appreciate your help please.
Thanks.

 

[p45cal]

Has Chandoo become a free coding service?

 

[maryclaire_g]

Hi. Sorry. I'm new here. I thought that I can ask for help here for coding?

 

[p45cal]

Help yes. To code according to your list of requirements, well...

 

[maryclaire_g]

Help yes. To code according to your list of requirements, well...

Hi. I've tried my best to make my own code. Here's what I tried by recording what I wanted to do. Unfortunately, it isn't able to highlight the cells that I want to be highlighted. The code works only to make the dates into the format that I want but it isn't able to copy and paste to the conditional formatting and do the highlighting. May I know what I can do to the code to highlight the cells containing the dates in D20, E21, and E20? I've also attached a picture for reference. Sorry to bother you. Appreciate any help please. Thanks.

>>> use code - tags <<<

Sub BondsTemplateAM()
'
' BondsTemplateAM Macro
'

'
    Range("C17:C18").Select
    Selection.Copy
    Range("D20").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("C17").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("D20:D21").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    Range("D20").Select
    Selection.NumberFormat = "ddmmmyy"
    Range("D21").Select
    Selection.NumberFormat = "ddmmmyy"
    Range("D20").Select
    Selection.Copy
    Range("E20").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("D20").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("E20").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    Selection.NumberFormat = "ddmmmyyyy"
    Range("D20").Select
    Selection.Copy
    Sheets("MM444R1").Select
    Columns("A:A").Select
    Application.CutCopyMode = False
    Sheets("list").Select
    Range("D21").Select
    Selection.Copy
    Sheets("MM444R1").Select
    Application.CutCopyMode = False
    Sheets("list").Select
    Range("D20").Select
    Selection.Copy
    Sheets("MM446R1").Select
    Columns("A:A").Select
    Application.CutCopyMode = False
    Sheets("list").Select
    Range("D21").Select
    Selection.Copy
    Sheets("MM446R1").Select
    Application.CutCopyMode = False
    Sheets("list").Select
    Range("E20").Select
    Selection.Copy
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    Sheets("MP446R1 ").Select
    Columns("A:A").Select
    Application.CutCopyMode = False
    ActiveWindow.ScrollWorkbookTabs Sheets:=-5
End Sub

 

[maryclaire_g]

Help yes. To code according to your list of requirements, well...

Also, the end goal looks like this in the picture I've attached which I can do using conditional formatting but there are many sheets so it's very time consuming.

 

[maryclaire_g]

Requesting to delete this thread please.