Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!
Subscribe for Free Excel tips & more!

Automatically Highlight Cells If Weekends Or Holidays

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

I'm beginner for excel.
I had been referring many excel example to get what I wanted to.
But still couldn't get the formula I need.
Does anyone can help?
I need the Sheet2 to highlight the cells with condition weekends and holidays.
And highlight with different color when leave is taken in Sheet1.
Is it possible to do so? (with the design I've created)

View Answers     

Similar Excel Tutorials

Get a Date that is So Many Working Days Before or After a Date in Excel - WORKDAY
How to find a date that is so many days before or after a specified date, excluding weekends and any holidays. This ...
Add Time Together Limiting by Working Hours and Excluding Weekends and Holidays in Excel
Add two times together to get a future date and time that falls within working hours, excluding weekends and any ho ...
Get the Number of Workdays Between Two Dates in Excel
How to calculate the total number of working days between two dates in Excel.  This allows you to exclude all weeke ...
Highlight Duplicate Values in Excel
How to highlight duplicate values in a list. Also, how to arrange those values next to each other so they are easy ...
Highlight Rows that Meet a Certain Condition in Excel
In this tutorial I am going to cover how to highlight rows that meet a certain condition. To do this I use the Cond ...
Highlight and Sort the Top and Bottom Performers in a List in Excel
How to highlight the rows of the top and bottom performers in a list of data. This allows you to quickly identify ...

Helpful Excel Macros

Highlight Cells which Contain Formulas
- This macro will highlight all of the cells in a worksheet which contain a formula. The first one listed will highlight
Automatically Highlight the Active or Selected Cell
- This macro will highlight the active cell in any excel spreadsheet with a color selected in the code. This means that a
Highlight Cells with Text or Formulas (non-empty cells)
- This macro will highlight all cells in excel which are not empty. This means that if a cell contains formulas, text, nu
Highlight the Row of the Selected Cell
- This macro will highlight the row and column of the active cell. This will fill the row of the active cell with a solid
Highlight the Row and Column of the Selected Cell
- This macro will highlight the row and column of the active cell. This will fill the column and row of the active cell w

Similar Topics

I have a workfile with Dates in column A. The Public Holidays have been set up in column B, but the public holiday dates are also in column A

I need conditional formatting the will highlight the weekend dates as well as the public holidays

I know how to set up conditional formatting to highlight weekends only, but not to incorporate Public Holidays as well. The formula below highlights weekends only. I need it amended so as to highlight public holidays as well.



Hello All

I have a workbook which is, in essence, a Gantt chart. However I want the weekends and holidays to be greyed out so that it stops people inadvertantly scheduling tasks for those particular days.

I have worked out how to grey out the weekends using Conditional Formating with this formula:

=IF(AND(ISBLANK(E1), OR(WEEKDAY(E$1, 2)=6, WEEKDAY(E$1, 2)=7)), 1, 0)

Anyone have any ideas how I can do the same to grey out public holidays??


I am creating a Gantt Chart and I am seeking assistance with including Weekends and Holidays as possible work days.

I am using the WORKDAY command to exclude weekends and known holidays; additionally, I am using the WEEKDAY command to evaluate weekends. I am stumped on how to work out a clean way to Include selected Weekend and Holidays as work days.

Any help will be greatly appreciated.

Hello All,
I'm stuck triyng to figure this out and could use your help. I am trying to calculate the total number of days excluding weekends and holidays. In column A I have the start date (1/2/2008 9:00 am), in column B I have the End date (1/8/2008 4:33 pm). In column C I would like to display the total number of days ( to 2 places (6.45)) excluding weekends and holidays. I tried networkdays but that only gives me whole numbers. Any help will be greatly appreciated.



I am trying to write an excel formula that will calculate the time difference (hours and minutes) between two dates (the time a ticket was opened and the time it was closed), taking into consideration only working hours (8:30 am - 5 pm) and excluding weekends and holidays.
For example:
Ticket opened on : Friday Mar 4 2011 4:00 pm
Ticket closed on : Tuesday Mar 8 2011 9:00 pm
Holiday ex: Mon Mar 7 2011

Then the answer should be: 1.5 hours

I am aware of NETWORKDAYS formula which handles weekends, any help is appreciated in adding to the formula to count for business hours and exclude holiday days (I suppose the holidays are to be stored seperately in the sheet).

Thank you.

Can anyone help? I have been asked to create a worksheet to calculate the percentage of the utilisation of a number of loan cars per month. Each of the cars may be used on each and every day of the week but the percentage utilisation is to be calculated on working days only. However it is essential to record the name of the driver of the car at weekends and holidays. Column A is populated with the dates of the month. Colum B is populated with the relative days of the week. In column C and subsequent columns I record the name of the driver of a particular car registration. Is there any way that I can count the number of drivers of each car on working days only and not weekends or bank holidays?

Can anyone help with this issue?

I'm trying to calculate the # of hours between the two dates, excluding holidays and weekends, with the standard work day equal to 8am to 5pm.

See the attached file for example. The highlighted cells should logically show 1.5 hours, but I always show 9 hours using network days.

Any help is appreciated.

Did anyone created a Function that calculate date backward and takes into consideration weekends and Holidays the way WORKDAY() function would.

The goal is to obtain a date based on a End Date and a number of days back in time and taking into consideration weekends (Such as Saturday and Sunday non working days) as well as Holidays and therefore the researched date would equal the number of days + any non working days and any holidays.

The resulted date should give us the same ending date when applying the Workday(Start_day,days,Holidays).

Looking forward to hearing from any one out there.

Your help will be much appreciated.

Note: this function will resolve my trouble when applying the "SS", "FS" and "FF" when creating a Gantt chart.

Thank you in advance!

Hi everyone,

Is there a way to highlight cells with a color when moving from cell to cell, the cells already have a border so when changing cells or doing a find, the cell border goes dimm and if doing lots of finds it's hard to see, I know the number of the row gets highlighted, is there a way to highlight the cell with a color, or highlight the border of the chosen cell with a different color?

any help is much appreciated.


I'm trying to highlight weekends in a pivot chart. I changed the shading color in a bar chart for each weekend day, however, the color reverts back to default when I change "Ad Slot Size" in the top left of the chart.

Any help is much appreciated.


Values in worksheet A are linked to cells in worksheet B. I need to
highlight certain values in A and have the highlight link to and remain with
the value in B. If I use a background color as a way to highlight certain
cells in A, the background color does not link to B. Also, I need the
highlight in B to be associated with the linked value in B - not the cell
location in B. If the highlight in B is associated with the cell in B, when
I do a row sort in B, the highlight becomes separated from the value in B.

Another way to pose the problem is to ask how to link formatted values with
formats in A with cells in B, so that the formatting in B is associated with
the vlaue in B, not the cell in B.

Hi All,

I want to built a formula which will list all working days in a month excluding weekends .i.e. saturdays and sundays and it will also exclude the list of the dates present in a different column .i.e. the holidays in that month and give us a list of working days. I have the below formula which will list down the dates excluding the weekends in a month if I put the first working day over it. for e.g. if my date is in cell A1 and cell A2 I enter the below formula and drag it down then it will give me the list of all working days.


This seem like it would be simple enough but im not sure if Excel has the functionality to do this. I know you can write code to highlight a cell (lets say cell A1) if a certain condition is met in that cell. But is it possible to highlight cell A1 if a certain condition is met in a different cell?

I am trying to have certain cells highlighted black until something is inputted in A1.

Thanks for any help.



I am looking to highlight a row depending on values of different cells within the same row.

I have implemented conditional formatting but once one condition is met stays like that. I would like to change the formatting if Condition that I class Higher importance to override any others.

A quick example:

A4:M4 = Title Row

Condition 1 - If there is value in cell H1 then highlight the row (A4:M4) "Green". If this condition is met please override any existing.

Condition 2 : If Cell H1 >G1+30 then highlight the row (A4:M4) "Red". If previous condition is (condition 3 below) then override but do not override Condition 1.

Condition 3: If cell D1 >C1+10 then highlight the row (A4:M4) "Blue". It can be over-ridden by condition 2 and 1.

Any help would be much appreciated

Many Thanks

I am making a chart to track stock quotes over a set peroid of time (last 7 quotes). I am able to get the information I want in the cells but when I apply the data to a chart and list the days the chart wants to fill in all dates, including weekends and hollidays.
Attached is a sample of the worksheet.


Excel is inserting holidays and weekend days into my time series. I want to
stop it doing this and to use only my date series.

I am trying to Highlight an entire row, if a vlue exists within a column. I have read many forums in which you can create a macro to do this. I have "10" values that I would like to use as far as values within a column. I would like to run the macro, and if a number exists, I would like it to highlight the entire row.
The cells that i would like to have read are within Columns "w" and "x". The cell values are within a merged cell because of the way that the program exports the report into excel.

The values that I would like to have met are :

96- Highlight Entire Row Orange
237-Highlight Entire Row Yellow
402-Highlight Entire Row Blue
680-Highlight Entire Row Blue
871-Highlight Entire Row Blue
827-Highlight Entire Row Blue
213-Highlight Entire Row Blue
833-Highlight Entire Row Blue
339-Highlight Entire Row Blue

Please let me know If I can post the excel spreadsheet, and whether or not I can be helped. Thanks !

Fellow Forum Members,
Is it possible in Excel 2007 to conditionally highlight in yellow alternating rows in groups of five rows? I have played around with the function below with no success:


How do I edit the function above so that Rows 1 thru 5 are highlighted yellow. Rows 6 thru 10 have no highlight applied, and Rows 11 thru 15 are highlighted in yellow, and so on an so on...?

The reason is because my A column has all of 2011 business days calculated (weekends excluded) and I want to highlight every other alternating working week. Any help greatly appreciated. Thanks.

Just got Office XP and in Excel I can't get my background color to work. I go to select a color and it selects but the color on the worksheet stays white. No gray, no highlight, nothing. Is there some setting that needs turned on/off?

Tried Format...cells....Pattern...Shading and no colors work.
Tried clicking the fill color tab on the toolbox and nothing.

Probably easy fix, but I haven't had XP long enough to figure it out.

I've been reading up on Excel's date and time functions and can't really figure out the best way of doing this. Any input would be appreciated.

I have a total amount of time that a machine should take to finish a task. I'd like to enter a date and time into a cell (Start Date) and have another cell return the date and time that the machine should be done with the task excluding weekends, and holidays (End Date). This would be based on a certain number of "working hours" (hours in the workday minus break periods) that would be calculated in another cell.

Has anyone done anything similar to this or could point me to some good info on it?

Thanks very much in advance


I am trying to develop a formula that adds 3 working days to a formula if the date stamp is after 10am, and 2 days if it is before. The cell containing the time I want to modify is E2. I have the following, but cannot work out how to rule out the weekends! I am sure networkdays is the way to go due to the public holiday factor, but just cant get my head around it!

=IF(MOD(E2,1)>0.41667,INT(E2) + 3,INT(E2) + 2)

I am trying to highlight the top 80% of products sold in a pivot table and when I use the rules of conditional formating it isn't working: Below is what I need to highlight. Any suggestions?

30.92% (Highlight in Red)
25.62% (Highlight in Red)
20.03% (Highlight in Red)
10.13% (Highlight in Red)

Does anyone know how to tell excel to highlight the active row or cell automatically when it is being typed in?

I have several cells that I would like to highlight if the value is 10% or greater of the value in B2. Is this the best approach?


If B2 = 50 and B3 = 5, there should be a highlight, but if the B3 value was changed to 4 the highlight goes away.

Any suggestions would be appreciated.

Working on this spreadsheet at work. Trying to figure out how to validate user input. Want to warn user if date being entered is a saturday, sunday or defined list of holidays. Also want to prevent these days from being entered. Any other day is OK.

Would appreciate any help and be specific. I have had no luck in creating tables and such. I must be doing something wrong. I am still getting up to speed on Excel's finer points.