I have a file that contains multiple rows of data. I built a macro that will then hide rows with a $0 balance. When the macro is completed, my spreadsheet is only showing the lines with a $ amount. (The $0 rows are hidden)
What I'd like to do is then format the unhidden rows so that each alternate row is color coded. This will separate the rows visually so I can more easily follow the rows across to view $'s per line.
I've attached a sample spreadsheet (it does contain the macro to hide the $0 rows). How can I then format the remaining rows with alternate shading?
I have a price list from a friend. The price list has to have every other row shaded. This part is no problem with the conditional format command.
The real trick, at least to me, is the background shade of the row is based on the first two letters in the left most column.
For instance:
Column A
CPST4 CPST5 FLADS3EC FLADS2EC FIL4
CP rows would alternate with one color while FL rows would alternate with a different color and FI rows would alternate with another different color. All other rows would have no shading.
MacOffice Excel 08 is used so no VB code can be used.
I have a worksheet that has rows of information up to row 3000. Sometimes all the rows are shown and sometimes a lot of them are hidden. When I try and print, the rows with info are printed but then the hidden rows are included as just blank pages. Is there a way I can get excel to print only the rows in 1-3000 that are shown and not hidden?
The small attached "Demo2" file indicates my problem. In the "Scorecard" sheet I'm using macros driven from listbox menus to hide or unhide rows on the "Tasks" sheet. It is VITAL that the rows on the "Tasks" sheet not be deleted or have new rows inserted. But, if I Protect them then the list box macros won't function. Is there a macro that will allow these macros to hide or unhide rows without allowing a user to add or delete these rows in the "Tasks" sheet? If possible a simple "global" solution is preferred as I have 100+ If/Then entries to contend with...
I dont know if a vb code is at all possible to copy a formula in a cell in row 2 straight down that column until the last unhidden row in which there is at least a cell populated with values from the 256 cells in that last unhidden row. Then in that column, I want the cells that are filled with formulas to be converted to values.
All these unhidden rows are autofiltered with certain criteria. The left and right columns to the formulated cells does not necessarily filled with values as they can be blank. Row 1 has header names, and a formula can be any cell in row 2 at any one time and that is exactly the problem i faced so that to record a macro seems totally useless for me because the cell in row2 where I will begin with a formula varies every time.
Currently I go a long way by taking these steps: Hghtlighting row1 Data/Filter/ Autofilter/ Select the header cell and click on drop-down filter Select (Custom...) with criteria Input formula on the cell in row2 Copy the formula Press Shift+ArrowDown keys until the last unhidden row Press Enter Undo Autofilter Select the column Copy and Paste as values Autofilter row1 again Select the header cell, (Custom..) equal "#N/A" and "zero" Continue next turn of formula..copy to last unhidden row, paste as values....
Me and other staff in my department are doing these long-winding steps endless of times, day in day out, until we are drained out and called quit. If there is a code that can do this, this is going to the greatest news ever for these people for that kind of work.
I managed to make hidden sheets unhidden. However, after doing so, the first sheet stays active. How can I correct this? EXCEL 2010.
Module4:
Code: Function Hide_Show(MySheets) ActiveSheet.Unprotect Password:="****" Application.ScreenUpdating = False For Each ws In Sheets X = Application.Match(ws.Name, MySheets, 0) If Not IsError(X) Then
[Code] .....
Sheet WORKSPACE:
Code: [Private Sub Open_BusinessDone_Click() MySheets = Array("WORKSPACE", "BUSINESS_DONE") Call Hide_Show(MySheets) End Sub
i have this code which inserts blank rows in alternate rows,
Code: Sub insertrow() ' insertrow Macro Application.ScreenUpdating = True Dim count As Integer Dim X As Integer For count = 1 To 20 If activecell.Value "" Then activecell.Offset(1, 0).Select
[code].....
what changes should i make in this code to insert rows only when ther are now blank rows. So first time i run, blank rows are already there, and when i update some data at the bottom and re-run it inserts blank rows again.
Can I automate an excel worksheet such that if ,say, "436" is written in a certain cell it shades that row yellow, or if "437" is in that cell it shades the row blue. It would then be easy to visually tell in an excel chart which lines belonged to ,say, specific companies.
Is there a way to shade alternating rows in a range and have it always be alternating no matter if a row is added in the middle of the range. If I do it manually now and add a row it messes up the shading and fixing it manually is very time consuming.
I do an analysis that contains 100 or more rows with 1 of 2 row labels in column A; "existing" or "retrofit." There may be numerous successive rows labeled "existing" in a given place where there is no retrofit. Rows labeled "existing" contain existing equipment details such as area, equip description, operating cost. Rows labeled "retrofit" contain recommended efficient replacements with details such as area operating cost and savings. I have to display total cost and savings. Right now I use the awful method, F1+F3+F4+F5... for existing and F2+F6... for retrofit. This is a terrible method, time consuming, prone to error, etc. Is there a way to total rows with specific labels when the row labels are not consistently alternating? Would like to attach an example spreadsheet but... the permissions say I may not, for some strange reason.
I have a single column of data and need to convert it into 2 columns, by alternating each row:
1
2
3
4
5
want to convert to:
1 2
3 4
5 6
I've seen a previous post from 2007 where someone asked for a formula to do the reverse of this. 'Domenic' provided a formula =INDEX($A$2:$B$6,INT((ROWS(D$2:D2)-1)/2)+1,MOD(ROWS(D$2:D2)-1,2)+1) which converted
I'm trying to use the following formula in conditional formatting in cell A2 when the range A2:AW64 is selected (this conditional formatting will be copied to all cells in the selected range):
Formula is: =isodd($A2)
The error I keep getting is this:
"You may not use references to other worksheets or other workbooks for Conditional Formatting criteria."
I'm trying to use this conditional formatting because column A is our "Order" column. "Order" can be from 1 to 13 and this column is sorted ascending. There can be multiple rows in each Order number. So I want to shade all 1's, 3's, 5's, etc... so that each Order group has alternating shading.
I also want to use conditional formatting, not VBA, to get this to work. In the future, rows might be added to an Order number or two and I want the shading to automatically adjust.
is there any easy way to fill alternate rows of a worksheet with a particular color? I have a worksheet with 175 rows and alternate rows are to be filled with green!
In Excel 2003, when choosing colors for cell patterns, it only gives me the option of 40 colors. How do I get it to offer me the "More colors..." that you see when coloring shapes that brings up the hexagonal color pallette?
I'm working on a criteria matrix in Excel 2010 that automatically plots a single member in a Scatter Chart based on the two values. There are 4 suppliers listed in Column D starting in cell D4 thru D7. The "x" value is listed in Column E starting in cell E4 thru E7. The "y" value is listed in Column F starting in cell F4 thru F7.
The scatter chart will plot the points correctly, however, there are two issues: 1) If I try to insert a data label using the "Series Name," or in this case, the supplier's name, it will lists ALL of the suppliers Column D. It will not list the single supplier listed in cell D4. 2) The scatter chart appears with gridlines as a 4x4 matrix with a total of 16 cells. The "x" and "y" axis both start at 0 and go to 4. I can shade the entire chart one color. However, I want to shade some of the cells with darker and lighter shades.
I am using Excel 2010 on XP. I would like to apply conditional formatting to shade every group in alternating color. For example, I have a list of US states. I have a formula already to produce this:
Current Conditional Formatting Formula: =MOD(SUM(1/COUNTIF($a$2:$a2,$a$2:$a2)),2)=1
STATES (unfiltered) AL AL AK AK AK AR AR AZ AZ AZ
When I apply a filter, the rows remain shaded as they were originally:
STATES (Filter OUT AK) AL AL AR AR AZ AZ AZ
I want the conditional format to change when I have filtered out items (DESIRED RESULT):
STATES (Filter OUT AK) AL AL AR AR AZ AZ AZ
I would assume SUBTOTAL(3,...) would need to be incorported into the conditional formatting formula above, but I do not know how.
i have a question with regards to the shading of a table in excel 2010.The grey/white shading of the sheet should adjust automatically when lines are inserted / deleted...what would be the most efficient way?
At the moment I do it all manually, and it takes forever. I am sure there must be a simple way of doing it. I am using Excel 2003, but could work in a newer version if required.
I have tried to define an alternative start-up folder (my goal is to have a PUBLIC.XLSB to store Macros every one can use)
BUT: When I do this, Excel does not start with any open workbook anymore. It starts with a blue background, and need to click "New" first for a workbook to open and work in.
How do I avoid this? One solution that would work, but requires the user to have Macros activated, is to add a workbook during the opening of Excel, but I would like to avoid this.
Is it possible to have a macro which navigates to next unhidden worksheet? I am currently using the macro noted below, but they pick up hidden worksheets. Next sheet:
I am calculating the average for a range of cells in a row. =AVERAGE(D7:P7). I need to hide some of the columns and calculate the average based only on the visible columns. How can I do that?
I have a workbook that has 2 sheets; "Quote Summary" and "ECS". The "ECS" sheet contains columns "C" through "L" each of which has a total on row "27". At any time, one or several (but never All) of these columns may be unused and therefore hidden.
I would like to know if it is possible to sum the total of only those columns that are NOT hidden and display that value in cel "C15" of the "Quote_Summary" sheet.
E.g. Date Jan 1, 2007 and it falls on on a Monday in the Day column. Date Apr 6, 2007 and it falls on on a Friday. Date Dec 25, 2007 and it falls on on a Tuesday.
What I want is to Colour Code the DAY column in case it is a holiday falling on a Monday or a Friday.
