Conditional Formatting - Highlight column Nth case of a letter

I have a spreadsheet I am helping my office administrator make. I have added some conditional formatting already, and I was hoping to add more. The structure is as follows:

Days of the week at the very top, travelling across the '1' row, listed as such: M Tu W Th F Sa Su. I cannot change them to full days of the week due to cell size restrictions for a printable version.

-On the left hand side I have employees listed. I want to mark holiday rows on this calender. For example, the third monday in February (third case of the letter 'M', with an if statement referencing the month cell, somehow highlighting the whole column where the third 'M' is)
-I was able to mark Saturday and Sunday off via conditional formatting, formulas, =B$1="Sa".
-My cell range is B1 to AF22.

Anyone have ideas? This will be a heavily accessed document and stability is paramount.

Unless your dates are going to be moving, why don't you just format the appropriate column manually before entering any data in it? From what you have described, there is no way to know which month, or even date, a particular column refers to.

Hope this helps.

Pete

Thanks for answering Pete!

The dates will be moving, as the first cell (B1) has to be manually entered. Then the remainder populate from a vlookup function.

I can have it manually formatted, I was just hoping for a spreadsheet I could circulate that would remain current from year to year.

To explain further: Cell A1 is the month column. So once you enter that first day of the week, the entire year populates and each month is already set in stone. Anything I can do here?

I don't quite understand how your data is laid out - perhaps you can post a sample workbook (the FAQ describes how to).

Pete

Example1.xlsx

If I read correctly, this should be what I'm talking about. In this example the third Monday in February is a holiday. I want it to highlight.

The CF formula that you need (with cells B3:AF4 selected) is:

=AND($A$1="February",B$1="M",COUNTIF($B$1:B$1,"M")=3)

Then click the Format button | Fill tab and choose your colour, then OK your way out.

Hope this helps.

Pete

I'm assuming that you want a spreadsheet that you can use as a template for any month of the year. I've made a few modifications to the example spreadsheet you attached. Those mods include: Enter the date for the 1st day of the month (I.E.: 2-1-2013)in the green cell (B4). Once the date is entered the day of the week will be automaticalls set for each date of the month. For instance, February 1st of this year falls on a Friday. Thus, the calandar will begin on a Friday. I added a row above the day of the week row. Each cell in this row has a drop down menu for selecting "H" for "Holiday". Selecting the "H" from the drop down menu highlights the column. In addition to that, columns are blacked out whose dates are not in the current month. For instance, there is no February 29th, 30th or 31st so those dates are blacked out. Normally I hide all of the stuff that makes this sort of application work, such as the top three rows, but I've left them unhidden so you can see what I did.

I would think that this sort of arrangement would give you the flexability to do what you want with the least amount of fuss. It also avoids the necessity of having to write or rewrite formulas every time you want to add a holiday.

I hope this helps... and since I'm a newbie here I hope the attachment actually did attach!


Conditional Formatting Nth column.xlsx