Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hee's another "for each" question
Assume the following "for each" loop:
for each column_in_loop in Range("A1:Z1") If ( column_in_loop.value = "something" ) then columns.select with selection .interior.colorindex = 3 .interior.pattern = xlSolid with end end if next ' column_in_loop The idea is to color the column that contains the "something" color "3" and pattern it "xlSolid" while leaving all other columns un-touched. In reality this loop colors and patterns everything in the A:Z columns "3" and "xlSolid" - not just the column whose first cell in the range ("A1:Z1") contains whatever is specified by "something" in the "IF." It's great to remove the color or pattern from the whole table is that were what I were trying to do. Any ideas on how to "tighten" it down so I can do just the one column which contains the what tested for in the IF? It's sure to be easy I just haven't found it yet. David |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hee's another "for each" question
for each column_in_loop in Range("A1:Z1")
If ( column_in_loop.value = "something" ) then with column_in_loop.entirecolumn .interior.colorindex = 3 .interior.pattern = xlSolid with end end if next ' column_in_loop -- HTH RP (remove nothere from the email address if mailing direct) "David F. Schrader" wrote in message ... Assume the following "for each" loop: for each column_in_loop in Range("A1:Z1") If ( column_in_loop.value = "something" ) then columns.select with selection .interior.colorindex = 3 .interior.pattern = xlSolid with end end if next ' column_in_loop The idea is to color the column that contains the "something" color "3" and pattern it "xlSolid" while leaving all other columns un-touched. In reality this loop colors and patterns everything in the A:Z columns "3" and "xlSolid" - not just the column whose first cell in the range ("A1:Z1") contains whatever is specified by "something" in the "IF." It's great to remove the color or pattern from the whole table is that were what I were trying to do. Any ideas on how to "tighten" it down so I can do just the one column which contains the what tested for in the IF? It's sure to be easy I just haven't found it yet. David |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hee's another "for each" question
David
David F. Schrader wrote: Assume the following "for each" loop: for each column_in_loop in Range("A1:Z1") If ( column_in_loop.value = "something" ) then With column_in_loop.EntireColumn .interior.colorindex = 3 .interior.pattern = xlSolid End With end if next ' column_in_loop The variable column_in_loop will be a single cell as your loop iterates through each cell in A1:Z1. When you get to the point where that cell equals "something", you turn the single cell reference into the whole column by using the EntireColumn property. If B1 = "something" then Range("B1").EntireColumn.Address will equal $B:$B. If there is only one column that will meet that criteria, put an Exit For statement inside your if block. That way, the loop won't check every cell in the range, but will stop when it gets to the one that matters. -- Dick Kusleika Excel MVP Daily Dose of Excel www.dicks-blog.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hee's another "for each" question
My thanks to you both. Both is these ideas
helped. The one got me what I wanted and the second helped me wring out a little more speed - always a nice consideration. David The virtual "Oreo" truck will be delivering a load of virtual cookies to each of you some time in the virtual future as a reward for your assistance. David. "David F. Schrader" wrote in message ... Assume the following "for each" loop: for each column_in_loop in Range("A1:Z1") If ( column_in_loop.value = "something" ) then columns.select with selection .interior.colorindex = 3 .interior.pattern = xlSolid with end end if next ' column_in_loop The idea is to color the column that contains the "something" color "3" and pattern it "xlSolid" while leaving all other columns un-touched. In reality this loop colors and patterns everything in the A:Z columns "3" and "xlSolid" - not just the column whose first cell in the range ("A1:Z1") contains whatever is specified by "something" in the "IF." It's great to remove the color or pattern from the whole table is that were what I were trying to do. Any ideas on how to "tighten" it down so I can do just the one column which contains the what tested for in the IF? It's sure to be easy I just haven't found it yet. David |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hee's another "for each" question
I look forward to virtually eating them :-)
Bob "David F. Schrader" wrote in message ... My thanks to you both. Both is these ideas helped. The one got me what I wanted and the second helped me wring out a little more speed - always a nice consideration. David The virtual "Oreo" truck will be delivering a load of virtual cookies to each of you some time in the virtual future as a reward for your assistance. David. "David F. Schrader" wrote in message ... Assume the following "for each" loop: for each column_in_loop in Range("A1:Z1") If ( column_in_loop.value = "something" ) then columns.select with selection .interior.colorindex = 3 .interior.pattern = xlSolid with end end if next ' column_in_loop The idea is to color the column that contains the "something" color "3" and pattern it "xlSolid" while leaving all other columns un-touched. In reality this loop colors and patterns everything in the A:Z columns "3" and "xlSolid" - not just the column whose first cell in the range ("A1:Z1") contains whatever is specified by "something" in the "IF." It's great to remove the color or pattern from the whole table is that were what I were trying to do. Any ideas on how to "tighten" it down so I can do just the one column which contains the what tested for in the IF? It's sure to be easy I just haven't found it yet. David |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hee's another "for each" question
I apologize for the delay in responding - got distracted
by one of those "I've got to have this report for my boss for the VP tomorrow and I need *you* to get all the data pulled together" kind of orders that took me away. To both of you - my thanks. Between these two ideas it now only works but it is faster too. David "Dick Kusleika" wrote in message ... David David F. Schrader wrote: Assume the following "for each" loop: for each column_in_loop in Range("A1:Z1") If ( column_in_loop.value = "something" ) then With column_in_loop.EntireColumn .interior.colorindex = 3 .interior.pattern = xlSolid End With end if next ' column_in_loop The variable column_in_loop will be a single cell as your loop iterates through each cell in A1:Z1. When you get to the point where that cell equals "something", you turn the single cell reference into the whole column by using the EntireColumn property. If B1 = "something" then Range("B1").EntireColumn.Address will equal $B:$B. If there is only one column that will meet that criteria, put an Exit For statement inside your if block. That way, the loop won't check every cell in the range, but will stop when it gets to the one that matters. -- Dick Kusleika Excel MVP Daily Dose of Excel www.dicks-blog.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I make a "tab name" the "chart title"? (question on this) | Charts and Charting in Excel | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
"Disk is Full" add-on question to "Can't reset last cell" post tod | Excel Discussion (Misc queries) | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel |