Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
auto-hide rows, cell format (# and @), update cell refs, shade cel
First, i'd like to apologize for the long winded post.
this was the best i can explain, in such short time:S (if i need to clarify on something pls let me know) If you can answer all, or ANY of my questions, in any way, i'd greatly appreciate it. first, A LITTLE BACKGROUND: I'm trying to write an excel template, that will reduce hours of work, in notepad, into minutes. I have a map made up of colored squares (named A1, A2, A3, B1, B2, etc etc) What i need to do is describe that map, one square at a time. (colors being represented by a Number) like... NAME, #, NameNorth, #North, NameEast, #EAST, NameSOUTH, #SOUTH, NameWest, #West So i trashed the notepad, and opened up EXCEL and made those the column titles. here's an example of the first 2 rows filled in: NOTE: I have other crap between row1 and 24. so the first square i work on, A-1, is in row25. NOTE ALSO: "0" means there is nothing there..like the edge of the map) A B C D E F G H I J COLUMN: NAME # North #N East #E South #S West #W ROW25: A-1 2 A-2 3 B-1 5 0 0 0 0 ROW26: A-2 3 A-3 2 B-2 4 A-1 2 0 0 (Row27 and beyond goes all the way down to square "O-20") this (somewhat of a) template fills in most of the blanks on its own. The "NAME" column starts already fillled in (from "A-1" to "0-20"), and never changes. The "#" Column is the only one i manually input data. EVERY OTHER COLUMNs' cell values update on their own, with a formula for each column, copied down. An example of C25's formula: =IF(B25=0,0,IF(B26=0,0,A26)) an example of D25's formula: =IF(B25=0,0,IF(B26=0,0,IF(H26=4,IF(B26=5,5,IF(B26= 6,6,4)),IF(B25=6,5,B26)))) (u dont have to understand it if u dont want to) anyway, so far, it works. Now, here are my problems..err.. "inconveniences". PROBLEM 1: [ auto-hide rows] I have 324 rows (A-1 thru A-20, B-1 thru B-20, etc, down to O-20). I will not be using every single row. is it possible to... Have some action bring up a pop up, asking me which rows to NOT hide i.e. DISPLAY ROWS: A-1 through A-[5] B-1 through B-[5] C-1 through C-[5] (...and so on, up to ...) O-1 through O-[5] (the [5] being a default, edittable value, if its possible to have one) A filled in example: DISPLAY ROWS: A-1 through A-7 B-1 through B-12 C-1 through C-14 [SUBMIT] <-- pressing would hide every other row between A-1 and O-20 (row 25 to row 324) that i didnt say to keep 'displayed'. method2: IF not a pop up, maybe if this was right on the sheet: column T U V W X row1 row2 UNHIDE: A-1 through A- [blank] row3 UNHIDE: B-1 through B- [blank] row4 (and so on, up to "UNHIDE: 0-1 through O- [blank] As soon as i fill in a blank cell, can i have excel take action immediately? (btw, i cant use add ons..) if these methods aren't possible, any other way to accomplish this would be appreciated. (AutoFilter wouldnt work for me. i could have it filter all the 'nonblanks' when i'm done...but the point was to hide the ones i WASNT going to fill, BEFORE i started..so they dont get in the way) PROBLEM 2: [wrap cell in quotes and other things] I want very cell in every column to have format with.. PREFIX: ' SUFFIX: ', but.. in the first column, it should be: PREFIX: "(' SUFFIX: ', and the last column: PREFIX: ')" SUFFIX: ' FOR EXAMPLE: COLUMN: NAME # North #N East #E South #S West #W ROW1: "('A-1', '2', 'A-2', '3', 'B-1' '5', '0', '0', '0', '0')" i've tried cell format. for columns with Numerical values: '#"'," for columns with text values: '@"'," (i dont know how to do the format for the first and last column. it wont allow me to have a single paranthese in a cell format.) anyway, there's another problem. In the example above, there is a "0" in the SOUTH and WEST columns (which are TEXT value columns) so how would i apply a NUMBER and TEXT format to a cell? if thats not possible, i think CONDITIONAL FORMAT will help. i just dont know how to do it. i.e. "IF cell value = 0", then <wrap cell value in single quotes or whatnot " just curious...but.. as a 2nd method.. would it be possible to apply these number/text formats thru a script of some sort (that i can activate when i'm finished working on the sheet)? and what if i wanted to remove the quotes, commas, and paranthese, the same way? how would i do this? the method for activation, preferrably, would be the click of a word in a cell like "WrapInQuotes" (as opposed to going to View Code, run, etc) PROBLEM 3:[update the cells that are being refered to?] Lets look at the formula examples again: An example of C25's formula: =IF(B25=0,0,IF(B26=0,0,A26)) an example of D25's formula: =IF(B25=0,0,IF(B26=0,0,IF(H26=4,IF(B26=5,5,IF(B26= 6,6,4)),IF(B25=6,5,B26)))) note, these are for the "A-1" square (row 25). this row's formula (of every column) is copied down to "A-20" (row 44). rows "B-1" through "B-20" have the same formulas, but the cell references are little different. the same goes for "C-1" through "C-20", "D-1" through "D-20" , and so on. So anyway.. after i have all these formulas entered for every cell.. Say i start describing a new map, which had the squares' columns shifted up or down. This would mean i'd have to change some of the cell references. Meaning, i want to somehow specify how much to add or subtract each cell reference by (in a formula), to have new cell references in that formula. and then i want that formula copied down for 20 rows. (i.e. From "A-1" 's row, to the "A-20" row) I want to specify this seperately for every 'square's column' (the "A-" column (A-1 thru A-20), the "B-" column (B-1 thru B-20), etc, up to the "O-" column (O-1 through O-20)) (I will try to explain better next time, if some1 didnt understand this) Here's an example. my command is(however i am to specify it): For the "A-" column, "add +2" to all cell references, except the cell reference that refers to the row itself. so this formula: =IF(B25=0,0,IF(B26=0,0,A26)) would change to: =IF(B25=0,0,IF(B28=0,0,A26)) and then the formula would copy down to row 44 (A-20), hopefully. Repeat the same questions for the "B-" column's cell references need changing in some way (by adding/subtracting a number) The same for the "c- " column, and so on. (NOTE: i am talking about changing the cells being referred to. NOT a value contained in the cell's reference. i dont know if that was clear.) PROBLEM 4: [ CELL SHADING] ok, i have 2 scripts for this (thanks to help from "Vergel Adriano" and this here site: http://www.ozgrid.com/VBA/excel-cond...ting-limit.htm ) the scripts will shade cells based on their values. but the script doesnt shade cells with existing values. and, it doesnt shade shells that have values that were inputted by cell formula. here's what i have: Private Sub Worksheet_Change(ByVal Target As Range) Dim icolor As Integer Dim c As Range If Not Intersect(Target, Range("A25:P344")) Is Nothing Then For Each c In Target Select Case c Case Is < 0 icolor = 3 Case 0 icolor = 51 Case 1 icolor = 45 Case 2 icolor = 4 Case 3 icolor = 10 Case 4 icolor = 5 Case 5 icolor = 48 Case 6 icolor = 9 Case Is 6 icolor = 3 Case Else icolor = 2 End Select c.Interior.ColorIndex = icolor Next c End If End Sub Vergel Adriano recommended the additional script: Sub ColorCells() Dim icolor As Integer Dim c As Range For Each c In Range("A25:P344") Select Case c Case Is < 0 icolor = 3 Case 0 icolor = 51 Case 1 icolor = 45 Case 2 icolor = 4 Case 3 icolor = 10 Case 4 icolor = 5 Case 5 icolor = 48 Case 6 icolor = 9 Case Is 6 icolor = 3 Case Else icolor = 2 End Select c.Interior.ColorIndex = icolor Next c End Sub This script works, but i have to run it again, everytime i edit a cell value. Is there a way to have this script, or somethign similar, continuously run? It might make the excel sheet run slower, so perhaps have the script check only the following cells: D25 through D324 F25 through F324 H25 through H324 J25 through J324 L25 through L324 N25 through N324 P25 through P324 if this script can be iniated by the click of a word in a cell (like "ColorCells") that would be awesome. and in the same , or similar method, have the script turn off. I'm also looking for a script that will change cells back to their original color (or no color at all) , altho this isn't necessary and may be too much to ask for (if i'm not doing that already lol) (Btw, i dont wanna use that CFPLUS add in, becuz any1 i give this file to, they would also be required to have the add in installed) ANY help would be appreciated with ANY of these questions. thanks in advance |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cell references auto update when sorting | Excel Discussion (Misc queries) | |||
when shade a cell don't see format on desktop | Excel Discussion (Misc queries) | |||
How do I fix cell refs when adding rows | Excel Discussion (Misc queries) | |||
Copy cell format to cell on another worksht and update automatical | Excel Worksheet Functions | |||
Auto update of cell information | Excel Discussion (Misc queries) |