VBA Code To Shade Rows With Like Values
Jun 17, 2014
I 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] ..........
View 8 Replies
ADVERTISEMENT
Nov 7, 2009
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 ) ...
View 9 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
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
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
Dec 6, 2012
I got the following code to sum values for columns from this post in the forums. It works perfectly
VB:
With xl.ActiveSheet.Range("A7").CurrentRegion
If .Cells(.Rows.Count, 1).value <> "Total" Then
With .Offset(.Rows.Count).Resize(1)
.Formula = "=SUM(R2C:R[-1]C)"
.Columns(1).value = "Total"
End With
End If
End With
However I would like to do the same for the rows of data in my sheet, with a "Totals" column added on to the first empty column to the right. To provide some background, my worksheet is very similar to a pivot table. So the actual data starts a few columns to the right from A, and it extends to the right for an unkown length. I am using xlToRight to set a variable 'a'.
I like this code because it doesn't Dim any new variables because this will have to be re used about 5 times in the context of a loop and I already have way too many variables going. But if I have to use them it is okay.
I tried modifying the above code with .FormulaR1C1 = "=SUM(RC[-11]:RC[-1])" to no avail.
View 1 Replies
View Related
Feb 15, 2014
I have a data sheet and I usually copy manually the values from different columns. I would like to paste them into another worksheet so that when I paste the values in Column A, I should not surpass Row100, and if so then the code should automatically shift to column B and start pasting. This should apply to all the columns till column F. Once column F is full till row100 then the procedure should start again from column A.
View 4 Replies
View Related
Feb 1, 2013
I need a macro that will count the number of rows that contain values in column C. I then need to Fill cells in column A with the text "product", cells in column D with text "P", and cells in column I with text "Right" until a blank is found in column C.
View 2 Replies
View Related
Aug 8, 2013
I am trying out with a code which checks for cell value as "Select" in column IU and then checks for corresponding column IV for value as "0". Please note that "Select" and "0" are populated by formulas. I need the select "Select" and "0" till the next "Select" occurs in column IU and delete the selected range and continue the process until last non empty cell based on column C.
I have written the below code but it doesn't work.
Code:
Public Sub Test()
Dim nRow As Long
Dim nStart As Long
[Code]....
I could have uploaded the excel file that I am working on but did not find any upload attachment option.
View 1 Replies
View Related
Jan 25, 2013
I have inherited a spreadsheet with over 800 rows (and daily expanding) and 14 columns of data. I've attached a sheet that looks similar, with only 200 rows for reference. Here are my questions:
1. How can I delete all rows that contain no values?
2. I want to fill an entire row of data yellow if the values in the final two cells (L&M) in the row are equal, and red if they aren't. How can I do that?
3. Column A contains only dates, from oldest to newest. I'd like an obvious visual clue for when the months change. Currently it's a long, merged, blue-filled cell that says "March 2011", for instance. It can't be color because all cells need to be filled based on certain criteria (see #2), and borders aren't obvious enough.
4. I want to click on the row number on the very right, but have it select only columns A-M, is that possible?
I'm also having trouble with autofill. It seems to only work on parts of the spreadsheet. I don't know what settings may have been changed, but I do have autocomplete turned on, and no clue what to do despite hours of googling.
Test Tracking Sheet.xlsx
View 4 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
Jun 4, 2014
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 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
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
Dec 15, 2006
I 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 Related
Apr 28, 2014
I have a graph and wanted to be able to shade in color gray the data that fall within highlighted.
Test.xlsx
View 1 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 12, 2013
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 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
Dec 1, 2011
What's the best way to shade in the area under a line graph?
View 1 Replies
View Related
Jul 24, 2009
I'm trying to write a VBA script which will delete all rows in my Excel spreadsheet where Column I (which contains a status code) does not contain the word "Completed".
At the moment, I'm doing this the other way round: my script is able to search for entries in Column I which contain the status codes "Pending", "Awaiting Authorisation", "In Progress" etc and delete them. The idea is that when all those rows are deleted, I'll only be left with rows which have a status of "Completed". This works fine at the moment. However, the concern is that if a brand new status code is added to the data file, my script would be unable to pick it up and delete it. This is a small sample of the code I'm currently using (which deletes all the rows with statuses other than Completed):
View 4 Replies
View Related
May 13, 2009
I have some buttons in different sheets in an excel file, each button has its own code, that is the reason I can not move the code related to each object to another location (sheet or module).
And I have one piece of code in Module1 (Auto_load) in order to execute automatically this routine every time file is opened. Inside "auto_load" routine I initialize some values of some check buttons,options buttons and positions of some objects in diferent sheets, but I can not pass the value of variables between Module and Sheet's code even when I declare as public variables and/or function.
I have the following structure: ...
View 11 Replies
View Related
Jan 30, 2013
I have a line chart that tracks daily inventory and daily distribution. The chart also has a static value of Max inventory and Min inventory levels. Is it possible to shade the area between the Min and Max levels on the chart a different color from the rest of the chart. For example the chart has a white background and I would like to shade the area betwen the min a max a light blue across the width of the chart.
View 4 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
Apr 5, 2012
How do I make my cell shade a certain color? For example, green if >90%, yellow if >70% and red if
View 6 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
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
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
View Related