I am trying to format a row (B) if B8 = Saturday or Sunday.
The value in B8 is derived from a formula.
I have tried the following but it only works if cell is typed manually.
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo stoppit
Application.EnableEvents = False
With Me.Range("B8")
If .Value = "Saturday" Or .Value = "Sunday" Then
Me.Range("B8:J8").Font.ColorIndex = 2
End If
End With
stoppit:
Application.EnableEvents = True
End
End Sub
I also want to change format back if cell changes to M-F.
I have an excel sheet that uses date formulas in order to provide data regarding the date that it brings up. My problem is that the formulas do not bring up dates unless the dates are on a weekday. Ex. I would like to look back 3 months in the past, and I do so using the formula =today()-90 (90 for 90 days, about 3 months). I would then like to input this date formula into another function (potentially an if function) so that if today-90 is a saturday or a sunday, the function will automatically bring up the date for the friday preceding it.
I have three columns named "name","start_date" and "end_date"
ex:
Name start_date end_date AB 9/11/13 10:19 AM ?? CD 9/12/13 11:45 AM ??
All i need to find is,
If Name = AB, i want put start_date+3 days in the end_date column, similarly for name = CD , start_date+7 days.
But the calculation should exculde weekends(sat and sun).
ex: For AB, start_date = 10/3/13 (3rd oct , thrusday), end_date should be start_date+3 ,so end_date = 10/6/13 but 6th october is sunday so the formula should give end_date as 10/7/13.
I am looking for Macro code preferably to get list of dates with Saturday / Sunday in a separate columns which falls Saturday and Sunday on imputing the year.
I have attached the xlx sheet. I am a truck driver and I always have to calculate my weekly wage on paper.
I start everyday at different times it varies the end of shift as well. We have day shift, night, saturday, sunday payrates. I have done an excel sheet I was trying for a couple of days reading forums and watching videos but just I couldn't.
Example if I start Friday at 18:00 and finisth Saturday at 04:00. I have in total 10 hours.
Day shift: 1 hour Night shift: 5 hour Saturday: 4 hour
So in one shift I get 3 different payrate. In one shift if I work more than 6 hours it must be deducted 00:45 minutes.
Day shift: 07:00-19:00 - £10.7 Night shift: 19:00-07:00 - £11.7 Saturday - £12.7 Sunday - £14.7
Attached File : DRIVER SHIFTS VARIABLE SHIFTS AND PAYRATES.xlsx‎
I am trying to write a macro to send mail on every Friday and also on last day of every month. If the last day of the month falls on Saturday or Sunday then the macro should mail on Friday. I have written a separate macro to send a mail. I have also written to check day(ie Monday, Tuesday etc) of today. If today is Friday or month end i can send mail. I dont know how to tell the macro to send mail on friday if the month last date is saturday or sunday.
Sub done() Dim Dat As Date, x As Integer, y As Date, sorry As String Dim str As String sorry = "Today is not friday or month end. So i cannot send mails" str = WeekdayName(Weekday(Now()))
I am trying to update a spreadsheet for 2012 to 2013 and want to use a formula to change SUNDAY 8 January to Sunday 9 January and so on for rest of year.
I have a Sheet ( Named "Summary" for Example ) of about 4,000 Rows that has a LOT of Conditional Formatting.
I Added Another 100 Rows this Morning and when I Tried Saving it a Message Saying that Not All the Formatting for the New Data that had Been Added had Been Saved. Is there a Macro or Something I can Run that will Make the Conditionally Formatted Cells Stay the Colour that they are but Delete the Conditional Formatting Part of it Achieved Using "Format" & "Conditional Format" from the Menu Please.
Ideally I would like to be Able to Enter the Number of Rows ( From Row ? to Row ? ) that I want this to Apply to.
I have five fields that have a conditional format applied to them: (see attached). Columns J, K, L, N and O. These conditionals highlight if a minimum number is entered. Ex. Minimum pushups for a 30 y/o male is 27, if a 26 is entered it highlights red. The total score (where I need this to perform) is calculated in column Q. It will format red if the total score is below 75 but what I can't figure out is how to make it format if any of the previous minimums have not been met.
Ex. In cell L4, his crunches were 25 (a automatic failure -- red formatting). Now the total score is above 75 so it calculates as passing (green), but I need it to highlight red regardless because of the minimum not met in cell L4.
I have a range which has conditional formats based on other cells. I want to copy this range into another sheet and retain the current formats as fixed formats without copying the conditional formulae. ideally in VBA.
Is it possible to change the format of cell AI3 based on the format of cell C3 and D3? I have C3 and D3 set to turn red based on what is in cell C2 and D2. I would like the following done:
If AI3=C3 & C3 is red, format AI3 blue If AI3=D3 & D3 is red, format AI3 blue Otherwise, leave AI3 unformatted.
I need to run a loop through a column of values (attachment col B) and when it finds a "J" it will apply conditional formatting to a row of 4 cells directly adjacent. The attachment is a theoretical before & after.
The attached workbook requires users to enter a date on the SAT sheet (B3). I would like that date to always represent Saturday (the first day of our work week) Is there a formula I can use to validate if the date entered by the user corrleates to Saturday or is there a way to auto-populate the workbook with the Saturday's date?
I have a tab called SAP DUMP where we copy our raw data to, from here we manually copy and paste into another sheet called ORDER TRACKING SHEET. When we do a fresh dump (overwrite) of raw data into the SAP DUMP tap we need to see if the there is a new entry which needs to be copied across to the ORDER TRACKING SHEET. I've got a vlookup to show this. The one I can't work out is that on the ORDER TRACKING SHEET I also need to see if there are any entries are now no longer on the SAP DUMP tab. This is where I thought a condition format might work, color the line red if it's no longer on the SAP DUMP tap. Can't make it work.....
I have a workbook with 12 worksheets corresponding to months. Each is formatted in A1 with the month (Date, Jan-01) and all rows below, starting at A5, are the days of the month (Custom, ddd d). All cells are linked so they change accordingly with A1.
I'm trying to apply conditional formatting so that all Fridays will have light grey shading across the row. I can get it to shade every 7th row using the MOD & ROW functions, but the shaded row changes with subsequent months and years (not necessarily Fridays). Can I get it to recognize every Friday through the year.
Disregard the First Table. I cant remove it. refer to the Second Table
This table is a Task Tracker. The Start Button Adds the Current Date in Enercon's Row C1 and the row below (C2) will automaticaly filled with color using Conditional Formating. My Question is how can I code a conditional format for Trail1 Rows. For example when Trail1 Cell is active then you click the start Button instead of Coloring D2 Cell it should Color D3 Cell.
Here is the Code in Start Button
Sub cmdStart_Click() Dim i As Integer Results = MsgBox("Are you working on " & ActiveCell.Value & " today ?", vbOKCancel, "") If Results = vbOK Then
With Sheets("Sheet1") i = .Cells(2, Columns.Count).End(xlToLeft).Column + 1 .Cells(2, i).Value = Now() i = i + 1
End With Else Range("A1").Select End If End Sub Regards, Kevin
Maybe I'm getting a blank, maybe the answer is so easy, but lately I can´t solve this: How do I use conditional format to change cell color if cell value (date) is not workday?
