Hee's another "for each" question

[David F. Schrader]

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

 

[Bob Phillips]

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)

 

[Dick Kusleika]

David

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.

 

[David F. Schrader]

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.

 

[Bob Phillips]

I look forward to virtually eating them

Bob

 

[David F. Schrader]

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