+ Reply to Thread
Results 1 to 4 of 4

Conditional Formatting - columns that are NOT current month

  1. #1
    Forum Contributor
    Join Date
    10-25-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    292

    Conditional Formatting - columns that are NOT current month

    I would like to format specific columns if their header cells do not contain the current month's date.

    In the screenshot below, columns N-Q are hard-coded with the grey formatting because in June those were the two columns that were from may. But, in July the "Orphan Week" as I've dubbed it, should be greyed out. I would like to make this automatic rather than me hard-formatting it.

    I've tried

    Please Login or Register  to view this content.
    and

    Please Login or Register  to view this content.
    neither of which work. What is the trick here?

    FYI: N3 is the only cell hard-coded with 6/27/17. P3 has =N3+1, etc. N4 and O4 have =N3, P4 and Q4 have =P3, etc.Typically, line 4 is hidden, but I revealed it in case for clarity o this question.

    Thank you.

    format-non-current-month-columns1.jpg

    format-non-current-month-columns2.jpg
    Attached Images Attached Images
    Last edited by eemiller1997; 07-04-2017 at 10:16 PM.

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,572

    Re: Conditional Formatting - columns that are NOT current month

    =Month(N$3)<>Month(Now())
    Ben Van Johnson

  3. #3
    Forum Contributor
    Join Date
    10-25-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    292

    Re: Conditional Formatting - columns that are NOT current month

    Quote Originally Posted by protonLeah View Post
    =Month(N$3)<>Month(Now())
    That looks great, thank you. I do have two things I need to adjust in that formula.

    1. I need the N$3 to cover two cells at a time because I want the pair of columns to be formatted at a time. Since N3 is merged, I need to use N4 and O4. When I tried N4:O4 that the entire highlighted section formatted.
    2. Instead of Now, I would like to specify the month. Would this be Month(7).... instead of Month(Now())?

    Thanks again.

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Conditional Formatting - columns that are NOT current month

    As for #1 try the following formula as a conditional formatting rule:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Note: the formula references M5 which is populated by a date (7/1/2017) and formatted 'mmmm'.
    As for #2 the argument for month needs to be a date which Excel stores as days starting with Jan 1, 1900, so if you use the number 7 Excel will take that to mean the month of Jan 7, 1900 which would be Jan. This is why I had the formula reference cell M5, so that you wouldn't have to keep changing the formula. Of course you could reference any cell, or you could 'hard code' the formula putting in =MONTH(DATE(2017,7,1))
    A spreadsheet with the aforementioned setup is attached. Test by changing the date in M5 to 6/1/17 and see if it is doing what you want.
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ 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] Copy Conditional Formatting Month to Month
    By kitttieluv in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-03-2016, 07:39 PM
  2. Replies: 1
    Last Post: 04-29-2016, 01:28 PM
  3. [SOLVED] Set PivotTable Filter to Current Day, Current Week, Current Month, or Current Year
    By EnigmaMatter in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-30-2014, 08:31 AM
  4. [SOLVED] Pick a cell containing current month actulas based what the current month is.
    By vanbasten007 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-10-2014, 01:17 AM
  5. Copy current month and delete entrys in 2 columns and move entrys in 2 columns
    By temar in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-18-2013, 09:54 PM
  6. Replies: 6
    Last Post: 12-09-2011, 03:17 PM
  7. Hide the current month columns
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 02-28-2006, 07:10 PM

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