VBA To SHADE Cells Based On: Step-size & Starting Cell
Mar 4, 2009
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:
View 14 Replies
ADVERTISEMENT
Jul 28, 2013
I 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?
View 3 Replies
View Related
Feb 17, 2013
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
View 9 Replies
View Related
Oct 15, 2009
I have one cell (B10) that will contain either "high", "medium", or "low". Based on one of these 3 values, I'd like to shade cell J15 a color with some white text. I got 1 of the 3 keywords working, not sure how to add in the other 2.
View 2 Replies
View Related
Jul 4, 2007
I have a spreadsheet where I am adding up figures in columns.
I need to differentiate between 4 different "types" of cells to sum.
Cells with figures in them are either green, red or have no fill; and some cells contain no figures at all.
At the bottom of the column, I need to total up all the figures in red cells, as well as all the figures in green cells.
The shading of these cells is not permanent - colours are changed as work progresses, so I need the totals to keep up with this.
If it cannot be done based on cell fill colour, is there any other way to do it, other than the usual long-winded way of @sum(..... etc
View 9 Replies
View Related
Jul 19, 2012
I have a gantt chart that was built in excel. I need to shade cells in the timeline based on persons title under each project.
I can provide a snip of what the layout looks like for a better visual. I want the PM's Green, Supers Yellow and Persons Reporting Orange.
To get the green bar shown is using formula: =SEARCH("Project Management",$E17) , The issue with this is it fails to consider the start finish dates. For this particular project it works but when the formatting is "dragged down" the issue arises.
View 9 Replies
View Related
Aug 6, 2013
I 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 Related
Jan 13, 2014
I have an tab with 20,666 rows that I need to separate into different tabs based on what a cell in a specific row starts with.
Data Tab
001-020-002-000 Walker River Irrigation DistPO Box 820 YeringtonNV89447
001-020-005-000 Walker River Irrigation DistPO Box 820 YeringtonNV89447
001-020-006-000 Walker River Irrigation DistPO Box 820 YeringtonNV89447
001-020-007-000 Walker River Irrigation DistPO Box 820 YeringtonNV89447
001-020-008-000 Walker River Irrigation DistPO Box 820 YeringtonNV89447
[|Code]...
So what I am looking to do is search the APN row in the Data Tab that starts with the number from the APN # row in the APN Tab and then copy the row to a new/existing tab named after the Description on the APN Tab.
I think that I have explained what I need to do
View 8 Replies
View Related
Jun 16, 2008
I am working on a macro and, as part of it, I would like to look at column A of my spreadsheet and remove all cell contents where the text in the cell does not start with the word "total." For instance, if the text of a cell in column H is "total money" I would like it to remain unchanged. However, if the text of a cell is "George Baker" I would like it removed. I ONLY want the text in column H to be considered ... no other column.
View 3 Replies
View Related
Mar 13, 2014
I'm using the VBA code below for a piece of code.
[Code] .....
However, I want to use this same function in another place, without the limit set on the cells that will be cleared.
What I would like to do is send the formula the cell to start at (E14), offset that by one column, and then send a number of rows. With that, create the range to be cleared.
Something like:
[Code] .....
View 3 Replies
View Related
Feb 3, 2014
I need to add something to the code below to have my first row of data entered into cell "Q8". As it is now the data is starting on row Q9 instead of Q8, but the rest of it is filling in correctly.
[Code] .....
View 1 Replies
View Related
Jun 5, 2008
I don't know what exactly is happening, but every step of the macro is happening every 1 second. Literally, the BPM is 60. It will execute a step in the macro every second, when in other macro's i've made, it zips right through as if it was on fast forward.
View 9 Replies
View Related
Dec 3, 2013
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 Related
Apr 4, 2013
I am having trouble with the code for this stepped scale rebate structure
$0 TO $50,000 NET PURCHASES=NO REBATE ISSUED
$50,001 TO $150,000=5% OF NET PURCHASES
$150,001 TO $1,000,000=8% OF NET PURCHASES
$1,000,001 TO $2,000,000=9% OF NET PURCHASES
$2,000,001 AND OVER=10% OF NET PURCHASES
I have the forumula for retro active to 50,000
=IF(O77<50001,"0",IF(O77<=150000,O77*5%,IF(O77<=1000000,O77*8%,IF(O77<=2000000,O77*9%,IF(O77>2000001,O77*10%,)))))
If the customer did 2.5 million. I need a formula that will multiply the first 150,000 by 5%, the next 849,999 @ 8%, the next 999,999 @ 9%, and the remaining 500,000 2 10%. I was thinking of using a min/max function.
View 6 Replies
View Related
Sep 7, 2006
I am having trouble with the code for this stepped scale commission structure.
Net Service
$1,1400
Step Scale Comm. %
$0-500 40%
$500-750 45%
$750-1000 47%
$1000-9999.99 50%
Final Commission Paid $ ?
View 7 Replies
View Related
Aug 1, 2008
I want to make a rectangle where the size of it is based on cell value.
View 9 Replies
View Related
Sep 1, 2009
Any 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.
View 4 Replies
View Related
May 22, 2009
I 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:
View 9 Replies
View Related
Dec 6, 2009
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.
View 12 Replies
View Related
May 18, 2006
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 Related
Dec 6, 2006
I have to make a macheine cycle chart. I have a column containing Step Names and a column containing step time in whole seconds. I need to paste the step names across Row 1 and whole numbers counting to X down colum A. I then need to have each second represented by coloring the cell, for example, Step 1 is 1 sec. and Step 2 is 2 sec. if "Step 1" is in B1 I need B2 colored then for "Step 2" in C1 i need C3-4 highlighted and so on for a variable number of steps. This is my first question so I hope I was clear enough. With this much programed I will be able to make simple changes to tweak it to fit my needs.
View 2 Replies
View Related
Mar 19, 2009
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.
View 4 Replies
View Related
Jul 18, 2013
I am trying to set up conditional formatting in cell C4 to change the font to a smaller number based on the value of C4. If C4 = 0 then Font Calibri 16, otherwise Font Calibri 24. However, the font size is not availabe, it is greyed out. Is there another way to accomplish this without using VBA? In the worksheet could I use an if() statement to change the font size?
View 6 Replies
View Related
Jan 18, 2014
Excel 2010. I need to place picture into one cell or one big merged cell, as a background fill. picture must resize to size of cell. must be fixed in, not in front. i still need write into that cell, so it needs to be really background.
View 2 Replies
View Related
Oct 28, 2013
I have a long chain of formulas calculating the return on a particular investment in equipment. We'll call the inputs A, B, and C, and the output (total savings generated) Z. There are several set combinations of values for A, B, and C that I'm interested in generating a summary report for.
There are a number of steps in-between the inputs that I'm interested in, all of which are necessary to get to the output, but is there a good way to have excel chart inputs and outputs without filling out the intervening data?
I suppose the whole thing is essentially a multi-variable, multi-step data table.
View 3 Replies
View Related
Jan 13, 2009
I 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.
View 9 Replies
View Related
Jun 3, 2006
I'm looking to set up a spreadsheet whereby individuals answer questions and have to format their answer using a particular font, colour, font size and so on. The idea is that I can then compare their answer sheet to a pre completed one using an =IF function and get a total score. The only problem is =if and =exact only lookup cell text/numbers and don't look at how the text is formatted within them.
View 9 Replies
View Related
Feb 9, 2007
Is 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 Related
Jun 27, 2013
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 Replies
View Related
Jan 14, 2014
way 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 Related
