Excel VBA for conditional formatting of sheets to color cells with date today and next working date (no weekends)

maryclaire_g

New Member
Joined
Feb 2, 2022
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
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.


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
 

Attachments

  • screenshot.PNG
    screenshot.PNG
    66.8 KB · Views: 15
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top