I have created a sheet which has 52 named ranges representing the week numbers.
I would like to apply VBA Conditional Formatting to each week with the following if a cell is empty colour the interior Green if not empty colour the interior Red.
I'm a rookie and looking for a simple way to display week date ranges as shown in the attachment for a year at a time.
View 11 Replies View RelatedI have the following data:
Column A = Date
Column B = Reservations made per day
For ex:
A B
1 3/1/2011 5
2 4/5/2011 10
3 3/8/2011 15
Then I have a look up table where based on the date ranges it assigns a week number.
WeekDATE Range 1Date Range 2
718-Feb-1124-Feb-11
825-Feb-1103-Mar-11
904-Mar-1110-Mar-11
1011-Mar-1117-Mar-11
1118-Mar-1124-Mar-11
1225-Mar-1131-Mar-11
1301-Apr-1107-Apr-11
1408-Apr-1114-Apr-11
1515-Apr-1121-Apr-11
1622-Apr-1128-Apr-11
I am looking for a fomula that would assign a week to the corresponding dates on column A and tha would then add all of the reservations booked for each week.
Need to write some kind of formula on Excel to count weeks backwards, year start 1 July till 30 june ,
View 8 Replies View RelatedI am trying to apply conditional formatting to the range K60:R82 based on their corresponding cell in the range K87:R109 (the arrays have the same size). If each cell in the first range is greater than their corresponding cell in the 2nd range, then the cell in the first range should be formatted with pink fill and red text.
View 2 Replies View RelatedAny way write a conditional format formula to shade a cell based on two ranges.
Here is what I need:
cell b4 should be colored in if n4:xfd4 = today's date AND n3:xfd3 = "Time Off". The part I am having trouble with is that it should only shade if today and time off are in the same column.
Here is an example:
This one should shade B4(note: Today is 08/13/2013):
time off
late
08/13/2013
08/10/2013
This one should not:
late
time off
08/13/2013
08/10/2013
This is what I came up with so far, but it shades the cell if either show up in the entire range:
=AND(MATCH($G$1,$n$4:$XFD$4),MATCH($H$1,$n$3:$XFD$3),1)
Note: g1 contains =today() and h1 = "Time off"
So formula is wrong.
I have got following numbers data and I want any number between the ranges repeat again or dupliacated it would highlighted as mentioned
ABC1Start RangeEnd RangeQty221002199100323002349504250025252652710271910621102299190729092929218234823492
As you can see yellow highlighted Ranges between the ranges are repeated again or duplicated that is why they are highlighted
In Excel 2007, is there a way to copy a conditional format from one cell to a range of cells. My conditional format is comparing the value of one cell say A1 to B1, and formatting the cell if the value is different. How do I copy this format to also compare A2 to B2, and C2 to D2 etc.
View 2 Replies View RelatedI am new to VBA & not sure of the full understanding of code copied from a workbook which worked on the same principle but with Monthly (12) tabs. I thought if modified to show weeks, the macro would be able to locate the current week tab & day/date within - but upon opening, the cell stops at WK19 & column O - rather than WK43, Column N (which changes daily).
Sub Auto_Open()
week(1) = "WK1"
week(2) = "WK2"
week(3) = "WK3"
week(4) = "WK4"
week(5) = "WK5"
week(6) = "WK6"
week(7) = "WK7"
week(8) = "WK8"
week(9) = "WK9"
week(10) = "WK10"
week(11) = "WK11"
week(12) = "WK12"
week(13) = "WK13"
week(14) = "WK14"
week(15) = "WK15"
week(16) = "WK16"
week(17) = "WK17"
week(18) = "WK18"
week(19) = "WK19"
week(20) = "WK20"
week(21) = "WK21"
week(22) = "WK22"
week(23) = "WK23"
week(24) = "WK24"......................................
i edited this thread becuase I realized why I was getting the wrong results, however, I am completely clueless on how I need to accomplish the task below.
I need to apply formatting based on five named ranges on another sheet. if the values in cell F1:F1000 appears in the named range "datestore" and the corresponding value in range h2:h1000 appears any of the named ranges "date1" through "date5" I would like it to fill the cell yellow.
[1] compare (row) ranges across two columns with an unequal number of rows (column A [number] to column C [number])
[2] save each result of [1] where there was a match in column B
[3] for each row where there was a match (now stored in B), compare the value from the same row in column D (date) with the same row of column F (date), and store the result in column E
[4] count and message box the final number of matches from column E
Data Example:
A B C D E F
----- ----- ----- ----- ----- -----
12345 match 12345 11/1/2011 match 11/1/2011
77777 34345 ... ...
A and C numbers match, so check same row date in D that does match date in E, place "match" in E, then count and display.
Sub other()
Dim range1, range2, range3, range4, range5, range6 As range
Dim x As Variant, y As Variant, z As Variant, a As Variant
[Code].....
I have a spreadsheet, a small section attached. There are near 20000 rows at present. I need to count the number of characters in B1 excluding any hyphen in b1. If a hyphen occurs in b on its own,the count is zero.If b cell is blank,i need a count of all characters in A.I need a formula I can autofill down.The count column is c and I have completed it manually to show what I mean.
View 9 Replies View RelatedI currently use the following formula in Conditional Formatting to highlight a persons birthday:
=$R1=DATE(YEAR($R1),MONTH(TODAY()),DAY(TODAY()))
which changes the colour of the cell on their birthday. However, I need to amend this formula so that the highlighting applies to the current week, as in Sunday thru to Saturday. My amendments have not been successful and you cannot use the WEEKNUM function in conditional formatting for some reason!
I was wondering if there's a way to add a formula to calculate week over week % change automatically every week when I enter in new data. see the attached excel file for reference.
What I would like to have is the ability for the formulas in c5 and f5 to be able to auto-update to the newest week and the previous week's data instead of manually having to update it each week. So if I were to add a new row with data for week beginning 12/2, the formula in c5 and f5 would automatically update to calculate the week over week variance. I tried researching prior to asking the question on this forum, and I think it may be possible to do it using the index match function, but I'm not sure how to apply it in this case.
I need to have a conditional format that will adjust based on percentage ranges.
1% to 25% would be shaded red
26% to 50% would be shaded Orange
51% to 100% would be shaded Green
I was hoping to reference another cell and have the formatted cell reflect the conditional format. I dont think that's possible, so I can include a percentage in another cell and have that highlighted.
I have a comparison model that looks at two weeks of data. I am trying to get around the deletion and insertion of records week on week. With the code below, I can currently find and correct the deletions and insertions to the list, and then resort the list so that the comparison will work.
Sub CheckForNewProjsRemovedProjects()
Dim MyCell As Range, oCell As Range, NewCell As Range
Dim Rng1 As Range
Set Rng1 = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
' Columns("B:B").Select
Range("B1:B" & Range("B" & Rows.Count).End(xlUp).Row).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
"C1"), Unique:=True
For Each oCell In Rng1
For Each MyCell In Range("C1:C" & Range("C" & Rows.Count).End(xlUp).Row)
If MyCell.Value = oCell.Value Then................
I have a running time sheet daily. It has 2 columns for Labor and 2 columns for travel
i.e. travel From / To 1300-1400 calculate 1 hour then travel home 1600-1700 1 hour this is calculated by the date entry 01/02/10 I have another calculation that tracks by the date i.e. 01/02/10 then Next job which all works fine.
The problem becomes how do I calculate a weekly total labor and travel by the date So added another column called weekly hour’s labor and use the Weeknum to determine which week is which day/date so the first Monday in January 2010 is week 2
2 problems
Having many multiple day / date entries are the same date x 7 days Monday –Sunday
(Relies on the date entered and the weeknum) 01/03/10 each line is complete however the dates carry over as does the time
When trying to calculate each row x 3 same date time then the value will be incorrect I need to calculate
Say 9.5 hour labor from the date 01/03/10 not 28.5 hours and then calculate the total weekly hours
01/03/10, 9.5 hours labor, 3 hours travel
01/03/10, 9.5 hours labor, 3 hours travel
01/03/10, 9.5 hours labor, 3 hours travel
i do have work and travel times for each job on the same line (separate columns) but I display the total here by date to summarize the totals
i have tried sum products and sumif to avail. I am using Windows XP SP2 with MS Office 2007
how do i calculate weekly hours by date and weeknum ?
Total Work per day
Total Travel per day
Daily....................
I am working on a project to import cellular phone usage data from Sprint into a workbook.
All cells in Column A are blank. Column B contains 1 instance of the cell phone account #, followed by dates of calls made, which continues until the next cell phone account # appears, and it’s calls made. The # of calls made obviously varies per account #.
The dates are in date format. The cell phone account # is in text format and is formatted with blue fill.
I need to copy the cell phone account # into column A for each line that has a date value in ColumnB, but the value copied must change when it reaches the next cell phone account #, so that the calls are matched up with the cell phone account # and can be imported into SQL.
Acct #
Date
Time
City Called from
State Called From
Called #
City Called
State Called................
I'm trying to write a formula that will tell me when its week one or week two, week three and week 4 based on a given date of any month.
I'm using weekday formula but no luck.
I have to download a report every week and have been manually checking the report weekly for changes.
View 5 Replies View RelatedI have a column where I am convering the Date into a Fiscal week number.
For example 10/6/2009 is Work week 41
Now I want to show October Week 41
I need to add the month and the text "Week" before the week number. what is the formula I use.
I need to copy the values of a range on the weekly sales worksheet to the monthly sales worksheet. The last column is the total on the weekly sales. Part of the heading of the total column is the week ending date (e.g. 10/17/2009. On the Monthly Sales I have the months in columns by week ending (e.g. 10/17/2009).
Range I4:I28 to the monthly sales worksheet by date.
Is it possible to format cells to convert a date format of month/day/year to = year/week #/day of week? For example, 04/05/07 (April 5, 2007) would read as 7145, (7=last digit of year/ 14 = week number / 5 = day of week....Sunday being the first day of week)
View 9 Replies View RelatedI need help creating a macro that will search through my excel spreadsheet and for every instance where column A isn't empty it should cut a range of columns from that row and paste them in a different range of columns in the row before it. It should then delete the row that it cut the columns from and keep searching until it has done this for the whole worksheet. I can modify which range of columns are needed, but it has been so long since I've worked with excel macros that I haven't been able to do it.
View 5 Replies View Relateddefinitions for static ranges and dynamic ranges?
View 9 Replies View RelatedI have two vertical ranges that I need summarized into 2 adjacent vertical ranges.
"
A B C D
| SUMMARY
model qty| modelqty
1 4.12922.0000| 4.12952.2000
2 2.000012.1250| 2.000025.1250
3 4.12929.0000| 318.0000
4 318.0000|
5 4.1291.2000|
6 213.0000|
"
A1:A6 is my SKU's model number B1:B6 is my inventory C1:C6 should contain formulas that result in a summary of the models D1:D6 should contain formulas that result in a sum of the inventory count for each model
In the attached file, I have variable range in column A:B, column C:D and in column E:F
I want a macro to do the following:
Start with sheet "A", select the available range in column A
then copy and paste in the sheet "B" but with all the cell values added with the value in H1.
Then in sheet A, simply copy the available range in column B and paste it in sheet B
Do the same until column F in sheet A. Pastespecial if it is odd column. simple past it is even column.
I know the macro code for the simple paste. But I am struggling with the paste special code.
How can i find the first day of a week
I have the week number and i need the date of monday of that week
Ex: Week number:24
First day of week(Monday):12-06-2006
I need the name of the day of the week from a date that has to be put together. This is going into a label on a UserForm.
I have a named range called wbDate. It is the month & year. The day of the month is the value of a TextBox (TBDay) on a UserForm. When I tried to combine these items, I used this
vToday = Month("wbDate") & " " & TBDay.Value & ", " & Year("wbDate")
vToday is dimmed as variant.
I am getting a type mismatch error and I'm not sure how to correct it.
What I am aiming for eventually, is the name of the day of the week.
I have written this macro to convert into a csv file to run for all defined named ranges in the activesheet. It run jst perfect when I hit SAVE button and it creates that many different CSV files for each named range.
However I am trying to use same macro in the another file and the problem I am facing is there a lot more named ranges and I want to run the macro for only selected NAMED RANGE. In this case 2 Named Range / 24 Named range.
What part of code do I need to change and to what to make it work for just 2 named ranges ?
