M
Mo2
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-conditional-formatting-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
344")) 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("A25344")
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
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-conditional-formatting-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
344")) Is Nothing ThenFor 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
344")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