For Each x in Range
 

I'm using Excel 2000. I have a range that I'm iterating through (For Each x
in Range...). If there is a number in the range, then I would like to change
certain characteristics of that particular cell (font, color, etc.) and then
have some other actions take place.

Is there a way to reference a particular cell this way (the only way I can
think of is to NOT use the For Each method, and instead, literally select
each cell in the range and use the Activecell.Offset method to advance to the
next cell, etc. I'd prefer to not have to do it this way, though.

Any ideas? Thanks for any and all help.

--
Craig

For Each x in Range
 

"Craig" wrote in message
...
Is there a way to reference a particular cell this way (the only way I can
think of is to NOT use the For Each method, and instead, literally select
each cell in the range and use the Activecell.Offset method to advance to
the
next cell, etc.

Why? That's a terrible idea.

Dim c As Range
For Each c In Range("A1:Z100").Cells
'If c.Value is whatever Then do whatever
Next

--

Vasant

For Each x in Range
 

That's NOT what I was asking....

I WANT to use the For Each method...what I don't know how to do is REFERENCE
THE ATTRIBUTES OF THE CELL THAT MEETS THE CRITERIA.

Any ideas would be greatly appreciated.

Thanks
--
Craig


"Vasant Nanavati" wrote:

"Craig" wrote in message
...
Is there a way to reference a particular cell this way (the only way I can
think of is to NOT use the For Each method, and instead, literally select
each cell in the range and use the Activecell.Offset method to advance to
the
next cell, etc.

Why? That's a terrible idea.

Dim c As Range
For Each c In Range("A1:Z100").Cells
'If c.Value is whatever Then do whatever
Next

--

Vasant

For Each x in Range
 

To follow up with my previous post, this is what I have so far. This
particular example finds an instance in the range where the cell value is a
number, then changes the text color to red, then prints the sheet. After
it's printed, it sets the color back to black (It doesn't work, though...)

Sub PrintThis()
For Each q In Range("Range").Cells
If q.Value < 0 Then
Selection.Font.ColorIndex = 3
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Selection.Font.ColorIndex = 0
End If
Next q
End Sub

--
Craig


"Vasant Nanavati" wrote:

"Craig" wrote in message
...
Is there a way to reference a particular cell this way (the only way I can
think of is to NOT use the For Each method, and instead, literally select
each cell in the range and use the Activecell.Offset method to advance to
the
next cell, etc.

Why? That's a terrible idea.

Dim c As Range
For Each c In Range("A1:Z100").Cells
'If c.Value is whatever Then do whatever
Next

--

Vasant

For Each x in Range
 

Pretty much the same as craig's, if the cell contains a number then it
changes the font color then displays a message box. The actions taken could
be anything. If this is not what you want, then you will have to be more
clear in your goal.


Private Sub Formatter()
Dim c As Range
For Each c In Range("Testrange").Cells
If IsNumeric(c) = True Then
c.Font.ColorIndex = 3 'or whatever
MsgBox "Color Changed" 'or whatever
End If
Next
End Sub

"Craig" wrote in message
...
I'm using Excel 2000. I have a range that I'm iterating through (For Each
x
in Range...). If there is a number in the range, then I would like to
change
certain characteristics of that particular cell (font, color, etc.) and
then
have some other actions take place.

Is there a way to reference a particular cell this way (the only way I can
think of is to NOT use the For Each method, and instead, literally select
each cell in the range and use the Activecell.Offset method to advance to
the
next cell, etc. I'd prefer to not have to do it this way, though.

Any ideas? Thanks for any and all help.

--
Craig

For Each x in Range
 

Instead of Selection, use the variable q. Am I missing something still?

--

Vasant


"Craig" wrote in message
...
To follow up with my previous post, this is what I have so far. This
particular example finds an instance in the range where the cell value is
a
number, then changes the text color to red, then prints the sheet. After
it's printed, it sets the color back to black (It doesn't work, though...)

Sub PrintThis()
For Each q In Range("Range").Cells
If q.Value < 0 Then
Selection.Font.ColorIndex = 3
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Selection.Font.ColorIndex = 0
End If
Next q
End Sub

--
Craig


"Vasant Nanavati" wrote:

"Craig" wrote in message
...
Is there a way to reference a particular cell this way (the only way I
can
think of is to NOT use the For Each method, and instead, literally
select
each cell in the range and use the Activecell.Offset method to advance
to
the
next cell, etc.

Why? That's a terrible idea.

Dim c As Range
For Each c In Range("A1:Z100").Cells
'If c.Value is whatever Then do whatever
Next

--

Vasant

For Each x in Range
 

Instead of working on Selection., use q.

Sub PrintThis()
dim q as range
For Each q In Range("Range").Cells
If q.Value < 0 Then
q.Font.ColorIndex = 3
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
q.Font.ColorIndex = 0
End If
Next q
End Sub

But each time you find a value < 0, you'll get a copy. Is that what you
wanted?

Maybe:

Sub PrintThis()
dim q as range
dim AllQ as range
For Each q In Range("Range").Cells
If q.Value < 0 Then
if allQ is nothing then
set allQ = q
else
set allQ = union(allq,q)
end if
End if
next q

if allq is nothing then
'nothing found
else
allq.font.colorindex = 3
end if

ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

if allq is nothing then
'nothing found
else
allq.font.colorindex = 0
end if

Next q
End Sub

Craig wrote:

To follow up with my previous post, this is what I have so far. This
particular example finds an instance in the range where the cell value is a
number, then changes the text color to red, then prints the sheet. After
it's printed, it sets the color back to black (It doesn't work, though...)

Sub PrintThis()
For Each q In Range("Range").Cells
If q.Value < 0 Then
Selection.Font.ColorIndex = 3
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Selection.Font.ColorIndex = 0
End If
Next q
End Sub

--
Craig

"Vasant Nanavati" wrote:

"Craig" wrote in message
...
Is there a way to reference a particular cell this way (the only way I can
think of is to NOT use the For Each method, and instead, literally select
each cell in the range and use the Activecell.Offset method to advance to
the
next cell, etc.

Why? That's a terrible idea.

Dim c As Range
For Each c In Range("A1:Z100").Cells
'If c.Value is whatever Then do whatever
Next

--

Vasant





--

Dave Peterson

For Each x in Range
 

Have you thought about using conditional formatting rather than looping
through the cells....

Sub test()
On Error Resume Next
With ActiveSheet
With .Range("Print_Area")
'if the print range has not been set
'use a range such as C1:R100
.FormatConditions.Delete
With .SpecialCells(xlCellTypeConstants, 1)
.FormatConditions.Add Type:=xlCellValue, _
Operator:=xlNotEqual, Formula1:=0
.FormatConditions(1).Font.ColorIndex = 3
End With
With .SpecialCells(xlCellTypeFormulas, 1)
.FormatConditions.Add Type:=xlCellValue, _
Operator:=xlNotEqual, Formula1:=0
.FormatConditions(1).Font.ColorIndex = 3
End With
End With
.Printout
.Range("Print_Area").FormatConditions.Delete
End With
End Sub

Alternatively, you could play with the number formatting...

Sub test1()
With ActiveSheet
..Range("Print_Area").NumberFormat = "[Red][0]#,##0;[Red][<0]#,##0"
..PrintOut
..Range("Print_Area").NumberFormat = "#,##0_ ;[Red](#,##0)"
End With
End Sub

--


XL2003
Regards

William



"Craig" wrote in message
...
I'm using Excel 2000. I have a range that I'm iterating through (For Each
x
in Range...). If there is a number in the range, then I would like to
change
certain characteristics of that particular cell (font, color, etc.) and
then
have some other actions take place.

Is there a way to reference a particular cell this way (the only way I can
think of is to NOT use the For Each method, and instead, literally select
each cell in the range and use the Activecell.Offset method to advance to
the
next cell, etc. I'd prefer to not have to do it this way, though.

Any ideas? Thanks for any and all help.

--
Craig

For Each x in Range
 

Yes. The line of code that I was missing was:

q.font.colorindex = 3

I know it sounds like a strange request. However, that's what the client
wants. To be able to loop through all the values in a given range, change
the font to red, then print out the same range over and over again.

Thanks to all for the insights and suggestions. I learned a lot.

C

--
Craig


"William" wrote:

Have you thought about using conditional formatting rather than looping
through the cells....

Sub test()
On Error Resume Next
With ActiveSheet
With .Range("Print_Area")
'if the print range has not been set
'use a range such as C1:R100
.FormatConditions.Delete
With .SpecialCells(xlCellTypeConstants, 1)
.FormatConditions.Add Type:=xlCellValue, _
Operator:=xlNotEqual, Formula1:=0
.FormatConditions(1).Font.ColorIndex = 3
End With
With .SpecialCells(xlCellTypeFormulas, 1)
.FormatConditions.Add Type:=xlCellValue, _
Operator:=xlNotEqual, Formula1:=0
.FormatConditions(1).Font.ColorIndex = 3
End With
End With
.Printout
.Range("Print_Area").FormatConditions.Delete
End With
End Sub

Alternatively, you could play with the number formatting...

Sub test1()
With ActiveSheet
..Range("Print_Area").NumberFormat = "[Red][0]#,##0;[Red][<0]#,##0"
..PrintOut
..Range("Print_Area").NumberFormat = "#,##0_ ;[Red](#,##0)"
End With
End Sub

--


XL2003
Regards

William



"Craig" wrote in message
...
I'm using Excel 2000. I have a range that I'm iterating through (For Each
x
in Range...). If there is a number in the range, then I would like to
change
certain characteristics of that particular cell (font, color, etc.) and
then
have some other actions take place.

Is there a way to reference a particular cell this way (the only way I can
think of is to NOT use the For Each method, and instead, literally select
each cell in the range and use the Activecell.Offset method to advance to
the
next cell, etc. I'd prefer to not have to do it this way, though.

Any ideas? Thanks for any and all help.

--
Craig

For Each x in Range
 

Bear in mind that any non-numeric entry will also be read as "<0". If all
the data is numerical that may not pose any problems.

Jon
"Craig" wrote in message
...
Yes. The line of code that I was missing was:

q.font.colorindex = 3

I know it sounds like a strange request. However, that's what the client
wants. To be able to loop through all the values in a given range, change
the font to red, then print out the same range over and over again.

Thanks to all for the insights and suggestions. I learned a lot.

C

--
Craig


"William" wrote:

Have you thought about using conditional formatting rather than looping
through the cells....

Sub test()
On Error Resume Next
With ActiveSheet
With .Range("Print_Area")
'if the print range has not been set
'use a range such as C1:R100
.FormatConditions.Delete
With .SpecialCells(xlCellTypeConstants, 1)
.FormatConditions.Add Type:=xlCellValue, _
Operator:=xlNotEqual, Formula1:=0
.FormatConditions(1).Font.ColorIndex = 3
End With
With .SpecialCells(xlCellTypeFormulas, 1)
.FormatConditions.Add Type:=xlCellValue, _
Operator:=xlNotEqual, Formula1:=0
.FormatConditions(1).Font.ColorIndex = 3
End With
End With
.Printout
.Range("Print_Area").FormatConditions.Delete
End With
End Sub

Alternatively, you could play with the number formatting...

Sub test1()
With ActiveSheet
..Range("Print_Area").NumberFormat = "[Red][0]#,##0;[Red][<0]#,##0"
..PrintOut
..Range("Print_Area").NumberFormat = "#,##0_ ;[Red](#,##0)"
End With
End Sub

--


XL2003
Regards

William



"Craig" wrote in message
...
I'm using Excel 2000. I have a range that I'm iterating through (For
Each
x
in Range...). If there is a number in the range, then I would like to
change
certain characteristics of that particular cell (font, color, etc.) and
then
have some other actions take place.

Is there a way to reference a particular cell this way (the only way I
can
think of is to NOT use the For Each method, and instead, literally
select
each cell in the range and use the Activecell.Offset method to advance
to
the
next cell, etc. I'd prefer to not have to do it this way, though.

Any ideas? Thanks for any and all help.

--
Craig

For Each x in Range
 

Hi Jon

I do not think you are correct - check the code.

--


XL2003
Regards

William



"Jon" wrote in message
news:aBJSe.215006$9A2.7676@edtnps89...
Bear in mind that any non-numeric entry will also be read as "<0". If all
the data is numerical that may not pose any problems.

Jon
"Craig" wrote in message
...
Yes. The line of code that I was missing was:

q.font.colorindex = 3

I know it sounds like a strange request. However, that's what the client
wants. To be able to loop through all the values in a given range,
change
the font to red, then print out the same range over and over again.

Thanks to all for the insights and suggestions. I learned a lot.

C

--
Craig


"William" wrote:

Have you thought about using conditional formatting rather than looping
through the cells....

Sub test()
On Error Resume Next
With ActiveSheet
With .Range("Print_Area")
'if the print range has not been set
'use a range such as C1:R100
.FormatConditions.Delete
With .SpecialCells(xlCellTypeConstants, 1)
.FormatConditions.Add Type:=xlCellValue, _
Operator:=xlNotEqual, Formula1:=0
.FormatConditions(1).Font.ColorIndex = 3
End With
With .SpecialCells(xlCellTypeFormulas, 1)
.FormatConditions.Add Type:=xlCellValue, _
Operator:=xlNotEqual, Formula1:=0
.FormatConditions(1).Font.ColorIndex = 3
End With
End With
.Printout
.Range("Print_Area").FormatConditions.Delete
End With
End Sub

Alternatively, you could play with the number formatting...

Sub test1()
With ActiveSheet
..Range("Print_Area").NumberFormat = "[Red][0]#,##0;[Red][<0]#,##0"
..PrintOut
..Range("Print_Area").NumberFormat = "#,##0_ ;[Red](#,##0)"
End With
End Sub

--


XL2003
Regards

William



"Craig" wrote in message
...
I'm using Excel 2000. I have a range that I'm iterating through (For
Each
x
in Range...). If there is a number in the range, then I would like to
change
certain characteristics of that particular cell (font, color, etc.)
and
then
have some other actions take place.

Is there a way to reference a particular cell this way (the only way I
can
think of is to NOT use the For Each method, and instead, literally
select
each cell in the range and use the Activecell.Offset method to advance
to
the
next cell, etc. I'd prefer to not have to do it this way, though.

Any ideas? Thanks for any and all help.

--
Craig

For Each x in Range
 

Wayne,
I'm sorry, I was referring to Dave's code not yours. I was looking at the <
0 portion. I have to confess I really didn't look closely at yours.

Jon

"William" wrote in message
...
Hi Jon

I do not think you are correct - check the code.

--


XL2003
Regards

William



"Jon" wrote in message
news:aBJSe.215006$9A2.7676@edtnps89...
Bear in mind that any non-numeric entry will also be read as "<0". If
all the data is numerical that may not pose any problems.

Jon
"Craig" wrote in message
...
Yes. The line of code that I was missing was:

q.font.colorindex = 3

I know it sounds like a strange request. However, that's what the
client
wants. To be able to loop through all the values in a given range,
change
the font to red, then print out the same range over and over again.

Thanks to all for the insights and suggestions. I learned a lot.

C

--
Craig


"William" wrote:

Have you thought about using conditional formatting rather than looping
through the cells....

Sub test()
On Error Resume Next
With ActiveSheet
With .Range("Print_Area")
'if the print range has not been set
'use a range such as C1:R100
.FormatConditions.Delete
With .SpecialCells(xlCellTypeConstants, 1)
.FormatConditions.Add Type:=xlCellValue, _
Operator:=xlNotEqual, Formula1:=0
.FormatConditions(1).Font.ColorIndex = 3
End With
With .SpecialCells(xlCellTypeFormulas, 1)
.FormatConditions.Add Type:=xlCellValue, _
Operator:=xlNotEqual, Formula1:=0
.FormatConditions(1).Font.ColorIndex = 3
End With
End With
.Printout
.Range("Print_Area").FormatConditions.Delete
End With
End Sub

Alternatively, you could play with the number formatting...

Sub test1()
With ActiveSheet
..Range("Print_Area").NumberFormat = "[Red][0]#,##0;[Red][<0]#,##0"
..PrintOut
..Range("Print_Area").NumberFormat = "#,##0_ ;[Red](#,##0)"
End With
End Sub

--


XL2003
Regards

William



"Craig" wrote in message
...
I'm using Excel 2000. I have a range that I'm iterating through (For
Each
x
in Range...). If there is a number in the range, then I would like
to
change
certain characteristics of that particular cell (font, color, etc.)
and
then
have some other actions take place.

Is there a way to reference a particular cell this way (the only way
I can
think of is to NOT use the For Each method, and instead, literally
select
each cell in the range and use the Activecell.Offset method to
advance to
the
next cell, etc. I'd prefer to not have to do it this way, though.

Any ideas? Thanks for any and all help.

--
Craig

For Each x in Range
 

And I was just matching the OP's original code.

Jon wrote:

Wayne,
I'm sorry, I was referring to Dave's code not yours. I was looking at the <
0 portion. I have to confess I really didn't look closely at yours.

Jon

"William" wrote in message
...
Hi Jon

I do not think you are correct - check the code.

--


XL2003
Regards

William



"Jon" wrote in message
news:aBJSe.215006$9A2.7676@edtnps89...
Bear in mind that any non-numeric entry will also be read as "<0". If
all the data is numerical that may not pose any problems.

Jon
"Craig" wrote in message
...
Yes. The line of code that I was missing was:

q.font.colorindex = 3

I know it sounds like a strange request. However, that's what the
client
wants. To be able to loop through all the values in a given range,
change
the font to red, then print out the same range over and over again.

Thanks to all for the insights and suggestions. I learned a lot.

C

--
Craig


"William" wrote:

Have you thought about using conditional formatting rather than looping
through the cells....

Sub test()
On Error Resume Next
With ActiveSheet
With .Range("Print_Area")
'if the print range has not been set
'use a range such as C1:R100
.FormatConditions.Delete
With .SpecialCells(xlCellTypeConstants, 1)
.FormatConditions.Add Type:=xlCellValue, _
Operator:=xlNotEqual, Formula1:=0
.FormatConditions(1).Font.ColorIndex = 3
End With
With .SpecialCells(xlCellTypeFormulas, 1)
.FormatConditions.Add Type:=xlCellValue, _
Operator:=xlNotEqual, Formula1:=0
.FormatConditions(1).Font.ColorIndex = 3
End With
End With
.Printout
.Range("Print_Area").FormatConditions.Delete
End With
End Sub

Alternatively, you could play with the number formatting...

Sub test1()
With ActiveSheet
..Range("Print_Area").NumberFormat = "[Red][0]#,##0;[Red][<0]#,##0"
..PrintOut
..Range("Print_Area").NumberFormat = "#,##0_ ;[Red](#,##0)"
End With
End Sub

--


XL2003
Regards

William



"Craig" wrote in message
...
I'm using Excel 2000. I have a range that I'm iterating through (For
Each
x
in Range...). If there is a number in the range, then I would like
to
change
certain characteristics of that particular cell (font, color, etc.)
and
then
have some other actions take place.

Is there a way to reference a particular cell this way (the only way
I can
think of is to NOT use the For Each method, and instead, literally
select
each cell in the range and use the Activecell.Offset method to
advance to
the
next cell, etc. I'd prefer to not have to do it this way, though.

Any ideas? Thanks for any and all help.

--
Craig








--

Dave Peterson