I have put together a duration schedule for work and have it set up now so the days with automatically fill if they are on or between the start/end date. Below is the formula that I have constructed. As of right now each activity is colored the same. I would like to have each activity match the color of the person performing it. My crew names are all in column A with a conditional format to be color coded with the name entered.
View 11 RepliesIm writing a spreadsheet that will act as a hotel room booking diary. There will be 52 sheets (representing each week of the year) and each sheet will therefore represent one week
Each sheet will look at operate the same. Running horizontally will be the days of the week and running vertically will be each of the bedrooms. There are 22 bedrooms in total and each room will have 12 cells allotted to it for each day - lets call this a 'block' and presume that the first block runs from C10 to c21. The final of these cells C21 will have a validation and the user can select 'PAID' 'TO PAY' or 'INVOICED'
If the user selects 'PAID' in C21 I want cells C10 through to C21 to shade GREEN
If the user selects 'TO PAY' in C21 I want cells C10 through to C21 to shade RED
If the user selects 'INVOICE' in C21 I want cells C10 through to C21 to shade YELLOW
Is there a way to make the attached worksheet automatically shade out all the Saturdays & Sundays in any given month everytime you change the Month/Year cell at the top of the worksheet, as example? I've tried using the weekday/Weekend formula, but can't quite get it right.
View 2 Replies View RelatedIs there anyway I can schedule an excel workbook to open up at a certain time; I have some code attached to the Workbook Open function; so when the workbook is opened the code will run; but I need the workbook to automatically open at say 17:00 everyday so the code can run.
View 9 Replies View RelatedI have a work sheet that has a date in a cell B7 and the system Date is Cell AB4.
What I wish for the sheet to do is shade the cell depending on the following criteria: If the date in Cell B7 is 2years and 10 months Before the System date to shade it Green, If the Date is Between 2 years 10 month and 3 Years before the System date shade yellow and if it is 3years and over shade Red.
For example if the date in B7 is 11-12-08 and the system date is 13-01-09 then the cell will be shaded Green.
I'm basically looking for a forumla that will count each employees total scheduled work days for the month inserted and then depening upon the day it is will show how many days the employee has left to work for the month.
View 9 Replies View RelatedIs it possible to use a formula to shade a cell dependent on a condition? I have tried an if formula (see below) but it is incorrect. =if('November 2006 SVOC'!B6>'March 2006 SVOC'!C6,'November 2006 SVOC'!B6 [red],if('November 2006 SVOC'!B6<'March 2006 SVOC'!C6,'November 2006 SVOC'!B6 [blue],))
View 3 Replies View Relatedway to shade/color a cell with a filter that is in use. . . that way, a user doesn't have to look at microscopic drop down filters to figure out which column is being filtered. .
View 1 Replies View RelatedI have a column with dates of July 1 of every year. How can I use conditional formatting to shade a cell a certain color based on July 1 of every year? The cell for July 2013 should be shaded, when 2014 comes, it is no longer shaded, then 2014 becomes shaded, & so on...
View 1 Replies View RelatedHow do I make my cell shade a certain color? For example, green if >90%, yellow if >70% and red if
View 6 Replies View RelatedI have a spreadsheet where I want to be able to Fill cells green in Column B to Column N for any Rows that have a value in Column L. All other Rows should remain the current colour (orange).
I can Fill the cells in Column L using conditional formatting but am not sure if I can use Conditional Formatting on other Columns so that their Fill colour is determined by the value in Column L?
I have a spreadsheet that will monitor payment schedules, in which both payment frequency and the payment start date are inputted by the user.
As such, to make filling out the column(s) fool-proof, I want to grey out cells in which data should not be entered.
For example, if the payment frequency is every 6th day, and the payments are to begin on day 0, then days 0, 6, 12 (etc) should be left white, whereas the remainder of the cells should be shaded.
I can achieve this using multiple conditional formatting rules in excel2007 with iterations of formulae of the type:
I'd like to shade a range of cells based on a particular cells value.
Specifically:
If cell E3 is JUNE then I'd like the range of cells from B10 thru I10 to be shaded.
If E3 is JULY then it would shade C10 thru I10....etc.
Excel 2010
I've adjusted a jonmo code to add an item in col B which is not in col A to the bottom of col A. - fab code, thanks jonmo.
But.. i want to:
insert rows beneath those in column A to accommodate the added items and shade those cells in list A once they been added ( so the users now they've been moved )
I've posted the code below ( including my attempts at colour change where it shade the right cell but in the wrong column ) ...
I just can't seem to get the syntax correct for setting or resetting a cell value using the OnTime schedule function. It looks like the cell value changes randomly and after several cycles it just runs non-stop very, very quickly and I have to use the ESC key to break out of the loop.
Here is my code:
VB:
Sub RunOnTime()
dTime = Now + TimeValue("00:00:10")
Application.OnTime dTime, "RunOnTime"
If Cells(1, 4).Value <= Cells(6, 2).Value Then
Cells(1, 4).Value = Cells(1, 4).Value + 1
[Code]...
I just can't seem to get the syntax correct for setting or resetting a cell value using the OnTime schedule function.
It looks like the cell value changes randomly and after several cycles it just runs non-stop very, very quickly and I have to use the ESC key to break out of the loop.
Here is my code:
Code:
Sub RunOnTime()
dTime = Now + TimeValue("00:00:10")
Application.OnTime dTime, "RunOnTime"
If Cells(1, 4).Value
I have been doing a lot of research on macros and vba scripts recently. I essentially want to email the value of a cell which I know is possible with a macro. To make things a little more difficult, I want to schedule this using Windows Task Scheduler. Here is my exact situation; I take care of a bill for my house. If my roommates carry a balance, I want them to be emailed once a week of how much they owe me. I want this macro to be independent of my excel file, meaning I don't want to do run every time I open the file or anything. I only want it sent once a week. If anyone could please advise on how to develop this script, I would greatly appreciate it. Please let me know if you need additional info.
Current system:
Windows Vista
Office 2007
Thunderbird email
I'm trying to use a formula in conditional formatting to highlight a cell red if the cell contains a 0 but the date shown in another cell has passed. I want to copy the formatting throughout a column but I don't want the cell to highlight if there is no date in the other cell concerned.
View 11 Replies View RelatedCan you have a cell shade (using a formula) if another cell has a validation list response with "NO" in Excel 2003?
View 2 Replies View RelatedAny time that any data is entered into one of the cells within my spreadsheet I would like the cell in which data is entered (and only the cell in which data is entered) to automatically become shaded (gray, preferably). I know conditional formatting should be used, but beyond that, I have no clue what to do. I would also prefer not to have to enter in conditional formatting formulas into all 105 cells, but I will do it if necessary.
Just in case, here's a little more information on what I am doing: I will be sending this spreadsheet out to about 10 others, and when they enter their name on a particular day at a particular time, I want that cell to become shaded. Other people might also enter their names at that day and time as well, so I would want the cell to stay shaded if that happens. Macros are not an option, because of security reasons.
I have a spreadsheet with columns a-v. Column F (titled aging) displays how many days old an issue is. I would like to have a macro that will automatically shade a row light red if the number is less than 90. I don't want the entire row shaded, just the contents of column a-v.
View 2 Replies View RelatedI am trying to create an event that would shade certain cells if a cell is active.
For example if the active cell is cell J39, shade the following rows as follows:
I need to add a Macro/VBA script to a spreadsheet i'm working on which will shade all cells, within a workbook, if the cell contains a formula (eg: =sum(A1:A5))
I can't get it to work under conditional formatting, nor with my limited knowledge of VBA.
I'm trying to come up with vba code that will gray out a set of cells when a certain value is enter in another cell.
View 9 Replies View RelatedI am trying to find a formula for my grade book. What I want to be able to do is shade every cell that has a value of zero (but not empty cells) so that when I print out the grade book, I can easily see all missing assignments.
View 4 Replies View RelatedI have a graph and wanted to be able to shade in color gray the data that fall within highlighted.
Test.xlsx
As I input data down a column in a wide Excel spreadsheet I would like to have highlighted the line I am working on as a check that I am in the correct place. I know that I can hit 'shift', 'space' for a single line, but is there a way to do it without having to hit those keystrokes each time?
View 2 Replies View RelatedWhat's the best way to shade in the area under a line graph?
View 1 Replies View RelatedI am fairly new to VBA and I am having some trouble with a VBA routine that was used in some workbooks I was given and I am trying to reuse that code.
What I am attempting to do is to shade the rows (alternate colors) whose value in Column C is the same until that value changes.
Example:
Row 1, 2 & 3 all have the same value in Column C so these would shade color 15
Row 4 thru 10 have a different value in Column C so those would have no shade
Row 11 thu 13 have a different value in Column C so these would then be Shade 15
So here is what I have done:
I set the range for the routine to step thru, then I defined the row & column to start processing. I then compare the current row to the row above. If the values are different then I compared the colors of the rows, and set the active row to the opposite color.
This routine runs but the first cell in each grouping isn't being shaded but then the rest of the rows are shaded correctly.
I have searched the web and look thru my code several times but to no avail, I cannot seem to understand why this will not work.
Code:
Sub grid_coloring()
Dim rwIndex, colindex, colindex2, ncount, bcount As Integer 'set rwindex, colIndex as an output number for columns. bcount and ncount are for counting blanks and dupes
Dim theRng As Range
Dim FirstRow, FirstCol, LastRow, LastCol As Integer
[Code] ..........
In B16:T24 I have cells that need shading depending on what code I put in them. I have 5 codes P, BH, S, ML, HD
I would like the cells to change colour, when I put P in them I want them to change to blue, when I put BH in them I want them to change to green, S I want to change to red and so on.