+ Reply to Thread
Results 1 to 2 of 2

help with vb script for adding dates so entire row is colored

  1. #1
    Registered User
    Join Date
    08-25-2008
    Location
    united states
    Posts
    12

    help with vb script for adding dates so entire row is colored

    Hello,

    I received some great help as to how to use code to color and entire row that has weekend dates. That code is below:

    Sub foo()
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
    With ws.UsedRange.FormatConditions.Add( _
    Type:=xlExpression, _
    Formula1:="=WEEKDAY($A1,16)<3")
    .Interior.Color = 12040422 'change to suit
    End With
    Next ws
    End Sub

    My next question - I was wondering if there is code for plugging in dates so an entire row get colored? These dates would be for holidays. Since some holidays change year-by-year (Veteran's Day, Labor Day, etc), I thought it would be easier to plug in the dates of these holidays. Instead of going to each month and manually highlighting the row.

    Any help would be appreciated.

    Thank you

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: help with vb script for adding dates so entire row is colored

    You could set up a table of dates, and descriptions if you wish on a separate worksheet. Assign it a (Dynamic) Named Range and then you could use COUNTIF, or MATCH to check if the date is present in the list. Maintenance of the list would be a manual task but you wouldn't need to change your CF

    So, for example, with a Dynamic Named Range, dnrHolidays, with the formula
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    and with a CF Formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Applied to your range, you could highlight whole rows or, perhaps better, selected columns.


    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Conditional Formatting - Dates to be Colored on Calendar
    By ExcelSponge in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-28-2015, 07:17 PM
  2. Replies: 4
    Last Post: 07-18-2015, 11:13 AM
  3. Adding Checkmarks and x's on dropdowns with colored columns
    By ZACH22213 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-10-2013, 08:26 AM
  4. Adding a colored shape (rectangle) to a footer
    By tommyfernandez in forum Excel General
    Replies: 2
    Last Post: 12-19-2012, 02:34 PM
  5. [SOLVED] Program a conditional script that will delete entire row if cell in column B is empty
    By Rabbitstew in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-17-2012, 01:54 PM
  6. [SOLVED] Deleting entire row if it is colored and then pasting noncolored rows in seperate TAB
    By amlan009 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-10-2012, 03:45 AM
  7. Replies: 4
    Last Post: 02-02-2012, 09:24 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1