Hide Rows Based On Color
Apr 4, 2008
I have the following code to hide rows based upon whether or not the cell in column A is black or not.
Sub hide_black()
Dim Rng As Range
Dim MyCell As Range
Set Rng = Range("A2:A36635")
For Each MyCell In Rng
If MyCell.Interior.ColorIndex = 1 Then
MyCell.EntireRow.Hidden = True
End If
Next MyCell
End Sub
I was thinking of taking this one step further and having the rows hide automatically once the cell is turned black allowing the user to bypass having to click on a macro button.
View 6 Replies
ADVERTISEMENT
Dec 28, 2009
I received this code which hides blank rows within a range.
View 12 Replies
View Related
Jan 15, 2009
I wrote a macro to color the cell values in the rows based on their average value. For eg if the cell value is less than 0.2 Avg, they should be red color,if value is between 0.2 and 0.5 it should be yellow. This part is working fine
Now based on the color of the rows cells , need to write a macro for the header one. Logic is Coloum header should be in red colour, if in one or more number of rows cells are red. same with yellow ones. Could you please help me out in solving this with logic.
View 8 Replies
View Related
Apr 9, 2014
Found a closed post on a solution that is almost what I am looking for, described by the following tab layout...
All tabs in worksheet: Master1, Tab1, Tab2, Master2, Tab3, Tab4, Master3, Tab5, Tab6
Click Master1: Visible - Master1, Tab1, Tab2, Master2, Master3
Click Master2: Visible - Master1, Master2, Tab3, Tab4, Master3
Click Master3: Visible - Master1, Master2, Master3, Tab5, Tab6
When you click a master tab, all tabs that are NOT the same color as that Master are hidden (except other masters)
This bit of VBA does the trick however I would like it to be color based. In this model below, you have to manually add the worksheets that belong to a particular master.
The behavior I seek is to associate a worksheet with a master by changing its tab color, and not having to do it in the code. I think it could be accomplished by cycling through the workbook and creating an array of tabs based on tab color.
'=============>>
Private Sub Worksheet_Activate()
Dim sh As Worksheet 'Object
Dim arr As Variant
arr = Array("Sheet1", "Sheet2", "Sheet3") ' <<=== CHANGE
[Code] .....
View 5 Replies
View Related
Nov 5, 2009
a way to hide used rows if the color of the cell in column I is green( color 35 vba) but only if the cell is visible will the macro change the rows visible state, in other words already hidden rows will remain hidden regardless if they match the color requirement.
below is some pseudo code to try to explain better what I'm trying to do. The below code doesn't actually work but I was wondering if someone knew some code that would actually do what I've described.
View 12 Replies
View Related
Feb 20, 2008
I am making a new stock card system to keep track of stock at work for a new storeman working in my department. What I want to be able to do it hide/show sheets bases on their colour, the reason I want to do this is because I want the new storeman to be able colour code products and only show certain ones at any one time to stop him from having to sort through loads of sheets, but also for him to be able to add extra sheets and asign them a colour and not need me to modify code and put it into category X So in summary, I want code that will hide all sheets that colour is red or blue etc and not need updating when new sheets are added.
View 4 Replies
View Related
Nov 3, 2006
I have a worksheet used for inventory. In Column A is the quantity (to be entered manually). In Column B is the product description. In Column C is the price of the product, and Column D the total price (column C price x the quantity entered in Column A). At the bottom of the worksheet is a grand total. Also, Column B (products) is grouped into subheadings by the supplier each product came from (for example, row 6 has the title PPG, and then rows 7-137 list every product from PPG).
The calculations in this worksheet work fine. What I am trying to do is, using a macro once all of the appropriate quantities are entered in column A, automatically hide every row of product that does not have a quantity. The tricky part is, if no products under a given supplier subheader are entered, the subheader also hides, and if a quantity is entered, that subheader shows. For example, if I have no quantities under any products for PPG, then the PPG subheader hides, but if just one quantity is added, PPG shows. Also, this list will be constantly updated, new products will be put in and taken out all of the time, so I cannot base the macro on a specific number of rows.
View 2 Replies
View Related
Apr 3, 2013
I'm trying to hide rows in an excel sheet based on two stipulations. I want to hide the row if it finds a particular value in column B and a different particular value in column K, otherwise i want it to do continue looping until it has hidden all rows that meet both stipulations.
VB:
Sub hide_loop()
Dim bl As Excel.Range
Dim blrange As Excel.Range
[Code]....
Currently I have tried different approaches, like a Do Loop, but I could not make that work, and this seems closer. The problem I'm having right now is that with this loop it hides everything found in column B regardless of what is in column K. I suspect this is because of the code following the if statement.''
View 9 Replies
View Related
May 7, 2014
I have a spreadsheet that is split in two parts , one with headers in row 16 with data flowing down to row 190. In row 192 I have another set of headers with data flowing down from that to row 300.
I have a userform (roughly at cell B13 in the attached) that filters the first block of data into either Company, Syndicate, EU Corporate or ALL. (ignore the other filters) which feature in range B18:B190
What I want it to do, is that when one of the three options is selected, ie Company, is that all rows from 193 down are hidden other than those that are also Company (in the test case there is just one row). The same is true for when Syndicate or EU Corporate are selected in the userform, and if the ALL is selected then none are hidden.
View 2 Replies
View Related
Jan 2, 2009
I need to write a macro that will hide several rows if a calculated date's month is not within the month of the report.
Example:
If month of date in cell B20 is not the same as the month of date in cell A5
then hide rows 20 thru 30 if it is the same month, display rows 20 thru 30.
I need to do this comparison twice. If month of date in cell B40 is not the same as the month of date in cell A5 then hide rows 40 thru 50 if it is the same month, display rows 40 thru 50. The dates in B20 and B40 are the result of a calculations (Date in cell A5 plus some number of days) if that makes any difference. I don't think it should.
View 3 Replies
View Related
Apr 11, 2009
I need to filter out rows, based on a specific value in column A, (documents on file for the clients), then check column C (last name) and D (client first name), which can have the same client listed multiple times, based on how many different documents are on file ......
View 12 Replies
View Related
Oct 6, 2009
What am I doing wrong here?
I have a code and it doesn't error out, but it won't hide the rows either. I'm pretty sure the red is what needs to be altered. I've tried adding "Selection.", "Rows." and "Cells." and none of them are working.
View 12 Replies
View Related
Dec 26, 2009
I want a code to Hide the row if "sum of numbers in columns three thru last column" = 0. Following is a code I use to delete a row if that is blank. The difference here is that I dont want to to check first two columns and I want to hide them rather than deleting.
View 4 Replies
View Related
Nov 18, 2011
I am using windows 7, and excel 2007. I want to hide rows bases on a value in cell G2 which is a date field
Example, if G2 is greater than 11/16/2011 then hide rows 1969 through 2032.
View 6 Replies
View Related
Mar 27, 2012
With a piece of code that will hide an entire row if a cell value in that row does not equal the cell value of another row.
Example
Cell d3 = Feb 2012.
Range C5:c252, = a mix of Feb 2012 and Mar 2012.
I want to hide all the Mar 2012.
However if Cell d3 = Mar 2012 then the range C5:C252 will also = Mar2012
I want to see all the rows within that range.
View 4 Replies
View Related
Apr 12, 2012
I am creating a survey and have used combox,i want hide rows if the result in the combobox is no
View 1 Replies
View Related
Apr 9, 2014
I am trying to auto hide rows in a spread sheet. the start row will always be 55 and the end row will be 55 + the value of cell A38 in sheet titled "Main".
View 2 Replies
View Related
Dec 30, 2009
I wrote some simple code to work through a number of rows and hide the rows which contain a value of "XXX". For some reason, when the code hits the line denoted with? it hides the first row of "XXX"'s but then starts all over again at very first line of code outside the loop.
Sheet1. Range("start_1").Select 'cell c53
Do Until ActiveCell.Value = "EOF" 'cell c266
If ActiveCell.Value = "XXX" Then
Selection.EntireRow.Hidden = True '????
ActiveCell.Offset(1, 0).Select
Else
ActiveCell.Offset(1, 0).Select
End If
Loop
View 2 Replies
View Related
Dec 13, 2013
In this example on Sheet 2, I'm trying to hide rows 1-6 if 'E6' is 0, and hide rows 15-26 if 'E18' is 0. These cell values are equal to the same cell in Sheet 1.
I've tried different codes I've found online, but I'm not very experienced with VBA so I can't get it to work. I can get rows 1-6 to hide, but it doesn't do it automatically whenever a zero is inputed into Sheet 1. It only works if I enter something into another cell on Sheet 2.
VB:
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("E6") = 0 Then
Rows("1:6").Select
Selection.EntireRow.Hidden = True
[Code]...
View 5 Replies
View Related
Jun 17, 2009
I'm using column A as a reference. My data starts in cell A3.
Whenever theres a zero in any cell in column A I would like that row to hide.
If the row was hidden and the value changes to any number greater than zero I need the row to unhide.
I can record two macros for the hide/unhide part of this but I dont understand how to run them based on cell value.
View 14 Replies
View Related
Jan 8, 2005
I have a shared worksheet that has many rows of data... but only certain rows are relevant to each specific users. I would like each user to be able to select their name from a drop-down list in order to have all rows that are not important to them be hidden. I can put the name of the user in column 'A' in each row they need to see, but how could I make all rows that do not contain their name in column 'A' hide? In other words, if 'Bill' is selected from a drop-down list, only rows that contain 'Bill' in column 'A' would be seen.
View 9 Replies
View Related
Jul 11, 2007
I am trying to implement the following:
After the user selects something using the validation list in cell F38, the macro should check in column Q in the rows 44 till 425 for "hide". For each row in which it finds "hide" the entire row should be hidden.
I have the code below but it does't work yet. It calculates for ages and afterwards only the first 2 rows of the 381 are hidden (probably because the third row is not "hide").
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyRange As Range
Dim ThisCell As Range
Target = Range("F38")
[code].....
View 9 Replies
View Related
Jun 17, 2003
Like to create a macro (button) to hide / unhide rows based on a cell value
Example
A1=No then hide roa A
C1=No then hide row c
View 9 Replies
View Related
Nov 16, 2011
I have a vba to hide certain rows based on 4 values (either 15, 20, 25, 30) are the choices in the 'ComboBox' drop down list. I also have the rows grouped so i need to make sure to collapse those back each time. Here is the vba i'm using, but its not quite right for some reason.
PHP Code:
Private Sub ComboBox1_Change()
If Range("J8").Value = "15" Then
Range("A185:A244").EntireRow.Hidden = True
Range("A245:A304").EntireRow.Hidden = True
Range("A305:A364").EntireRow.Hidden = True
Range("A365").EntireRow.Hidden = False
[Code]...
View 1 Replies
View Related
Apr 30, 2012
I have a worksheet "Feature Segments" that has a value in cell B40 and C40, if the value in these is "Off (Default)" then I need rows 22 and 23 on Worksheet "Summary" to hide.
I have put this code on the features segment tab but it is not working.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
If Target.Address = "B40" Then
[Code].....
View 3 Replies
View Related
Jan 29, 2013
a code that will search each cell across 4 columns and hide the row only if all cells are blank. The macro should search columns "b", "c", "e", and "f" to display all rows where at least one of the cells has a value.
Ex.
Col.B Col.C Col.D Col.E Col.F
1. 123 xxxxx 150
2. 56 xxxxx 50
3. (blank) (blank) xxxxx (blank) (blank)
In this ex. row 3 would be hidden.
View 1 Replies
View Related
Apr 1, 2013
I am creating a copy of a spreadsheet (table format) in a separate workbook using formulas that will update the copy as the original updates. I would like to auto hide the rows that have the value "Returned" in column G. I have columns A - G , rows 2 - 2000. The value "Returned" is the result of a simple = formula. Is there some way to accomplish this?
View 4 Replies
View Related
Apr 11, 2013
I have a set of 3,000 data points. The data is in one second intervals, however I am interested in seeing what the data looks like in a graph if I only had a point every minute. I have the following set up.
A B C
TIME X Y
What I want to do is to leave the first cell unhidden, and then hide the next 59. And then repeat this so I narrow the data down to one second each. I'm looking to do this multiple times, so I can create a graph each time in order to get an idea of what the data would look like if I only had a point for every minute.
Is there a macro that I could write, that would hide every cell except every 60th one? I was thinking that I could hide it based on the time. What would the code look like if my time was in 00:00:00 format? It's increasing, so I need it to hide cells that aren't equal to XX:XX:01, for example.
View 3 Replies
View Related
Feb 21, 2014
I am attempting to hide a series of rows based on if the cells in that row are blank. The catch is that the field of data in the column may vary as follows:
D E F G
x x X x
x X x
x
I would like to eliminate all the rows past the last X value in Column D for example
Below is the code I am attempting to use
Rows("41:60").Select
ActiveWindow.ScrollRow = 41
ActiveWindow.ScrollRow = 42
[Code].....
View 8 Replies
View Related
Jan 8, 2005
I have a shared worksheet that has many rows of data... but only certain rows are relevant to each specific users. I would like each user to be able to select their name from a drop-down list in order to have all rows that are not important to them be hidden. I can put the name of the user in column 'A' in each row they need to see, but how could I make all rows that do not contain their name in column 'A' hide? In other words, if 'Bill' is selected from a drop-down list, only rows that contain 'Bill' in column 'A' would be seen.
View 9 Replies
View Related
