The following generates an error "Next" without a "For". What do I put in so
that it formats the cell which corresponds to the following row & column
parameters?

For a = 4 To 31
If Cells(a, 1).Value = "John" Then
For b = 2 To 32
If Cells(2, b).Value = "Fri" Then
Cells(b, a).Select
With Selection.Interior
.ColorIndex = 0
.Pattern = xlGray8
.PatternColorIndex = xlAutomatic
End With
Next b
End If
Next a
MsgBox "Formatting Complete"
Exit Sub
Error:
Exit Sub
End Sub


Thanks

On Tue, 1 Jun 2004 20:46:51 -0700, John Keturi wrote:

The following generates an error "Next" without a "For". What do I put in so
that it formats the cell which corresponds to the following row & column
parameters?

For a = 4 To 31
If Cells(a, 1).Value = "John" Then
For b = 2 To 32
If Cells(2, b).Value = "Fri" Then
Cells(b, a).Select
With Selection.Interior
.ColorIndex = 0
.Pattern = xlGray8
.PatternColorIndex = xlAutomatic
End With
Next b
End If
Next a
MsgBox "Formatting Complete"
Exit Sub
Error:
Exit Sub
End Sub


Thanks

Be a bit more consistent with your indenting, and this sort of error
would stick out like a sore thumb:
For a = 4 To 31
If Cells(a, 1).Value = "John" Then
For b = 2 To 32
If Cells(2, b).Value = "Fri" Then
Cells(b, a).Select
With Selection.Interior
.ColorIndex = 0
.Pattern = xlGray8
.PatternColorIndex = xlAutomatic
End With
Next b
End If
Next a

The problem is there's no End If for the second If. Also, VB doesn't
need "Next b" and "Next a" - you can leave them both as just "Next" with
no problems.
--
auric underscore underscore at hotmail dot com
*****
- Suck the marrow out of life, that's what I say.
- But will you swallow?
- NO, smarty pants. Well, not on the first date, anyway.

You are missing an End If right after the End With.

--

Vasant

"John Keturi" wrote in message
news:G4cvc.40419$mm1.15189@fed1read06...
The following generates an error "Next" without a "For". What do I put in
so
that it formats the cell which corresponds to the following row & column
parameters?

For a = 4 To 31
If Cells(a, 1).Value = "John" Then
For b = 2 To 32
If Cells(2, b).Value = "Fri" Then
Cells(b, a).Select
With Selection.Interior
.ColorIndex = 0
.Pattern = xlGray8
.PatternColorIndex = xlAutomatic
End With
Next b
End If
Next a
MsgBox "Formatting Complete"
Exit Sub
Error:
Exit Sub
End Sub


Thanks

Hi John
If I understand what you are trying to do is to shade intersecting cells
where column A contains John and row 2 contains Fri ?
If I'm right then you need to change your code as follows.....

Sub test()
Dim a As Integer, b As Integer
For a = 4 To 31
If Cells(a, 1).Value = "John" Then
For b = 2 To 32
If Cells(2, b).Value = "Fri" Then
With Cells(a, b).Interior
.ColorIndex = 0
.Pattern = xlGray8
.PatternColorIndex = xlAutomatic
End With
End If
Next b
End If
Next a
MsgBox "Formatting Complete"
End Sub

Cheers
Nigel

"John Keturi" wrote in message
news:G4cvc.40419$mm1.15189@fed1read06...
The following generates an error "Next" without a "For". What do I put in
so
that it formats the cell which corresponds to the following row & column
parameters?

For a = 4 To 31
If Cells(a, 1).Value = "John" Then
For b = 2 To 32
If Cells(2, b).Value = "Fri" Then
Cells(b, a).Select
With Selection.Interior
.ColorIndex = 0
.Pattern = xlGray8
.PatternColorIndex = xlAutomatic
End With
Next b
End If
Next a
MsgBox "Formatting Complete"
Exit Sub
Error:
Exit Sub
End Sub


Thanks

"Auric__" wrote in message
...
. Also, VB doesn't
need "Next b" and "Next a" - you can leave them both as just "Next" with
no problems.


But you wouldn't do that would you? It's okay in small, simple code, but in
long complex code it is far better to include it for helping to know where
you are when debugging.

On Wed, 2 Jun 2004 07:19:26 +0100, Bob Phillips wrote:


"Auric__" wrote in message
.. .
. Also, VB doesn't
need "Next b" and "Next a" - you can leave them both as just "Next" with
no problems.


But you wouldn't do that would you? It's okay in small, simple code, but in
long complex code it is far better to include it for helping to know where
you are when debugging.

If you indent consistently, it's just a matter of looking for where the
code gets indented to that level.
--
auric underscore underscore at hotmail dot com
*****
- With a program like this, I CAN RULE THE WORLD!
- Rule quietly, please? My liver doesn't like loud noises, and right now
I'm trying real hard not to make it angry...

In a large complex program that can become unmanageable, whereas taking the
simple step of explicitly stating the counter solves the issue and is no
overhead.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Auric__" wrote in message
...
On Wed, 2 Jun 2004 07:19:26 +0100, Bob Phillips wrote:


"Auric__" wrote in message
.. .
. Also, VB doesn't
need "Next b" and "Next a" - you can leave them both as just "Next"
with
no problems.


But you wouldn't do that would you? It's okay in small, simple code, but
in
long complex code it is far better to include it for helping to know
where
you are when debugging.

If you indent consistently, it's just a matter of looking for where the
code gets indented to that level.
--
auric underscore underscore at hotmail dot com
*****
- With a program like this, I CAN RULE THE WORLD!
- Rule quietly, please? My liver doesn't like loud noises, and right now
I'm trying real hard not to make it angry...

On Wed, 2 Jun 2004 12:54:59 +0100, Bob Phillips wrote:

In a large complex program that can become unmanageable, whereas taking the
simple step of explicitly stating the counter solves the issue and is no
overhead.

I guess it boils down to personal preference, really.
--
auric underscore underscore at hotmail dot com
*****
31337 is a prime number. Go figure.