Visual Basic Help

I am writing some code in a macro, that when a checkbox is checked, the macro
is run and will look at a cell to see if the word "TRUE" is in it. If it is
true, I want to turn the cell red. If it is not true, I want the cell to be
white. I am getting an else without if error. Below is the code, could
someone help me?

Sub us()
'
' us Macro
' Macro recorded 12/22/2005 by
'

'
If Range("M42") = "True" Then
ActiveWindow.SmallScroll Down:=4
Range("D42,D44,D46").Select
Range("D46").Activate
ActiveWindow.SmallScroll Down:=3
Range("D42,D44,D46,D48,D50").Select
Range("D50").Activate
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
Else
ActiveWindow.SmallScroll Down:=4
Range("D42,D44,D46").Select
Range("D46").Activate
ActiveWindow.SmallScroll Down:=3
Range("D42,D44,D46,D48,D50").Select
Range("D50").Activate
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End If
End Sub

Don't you need an End With after your With blocks?

You need to close your With sections with End With. Note inserted lines
below
Paul D

"John" <[email protected]> wrote in message
news:[email protected]...
: I am writing some code in a macro, that when a checkbox is checked, the
macro
: is run and will look at a cell to see if the word "TRUE" is in it. If it
is
: true, I want to turn the cell red. If it is not true, I want the cell to
be
: white. I am getting an else without if error. Below is the code, could
: someone help me?
:
: Sub us()
: '
: ' us Macro
: ' Macro recorded 12/22/2005 by
: '
:
: '
: If Range("M42") = "True" Then
: ActiveWindow.SmallScroll Down:=4
: Range("D42,D44,D46").Select
: Range("D46").Activate
: ActiveWindow.SmallScroll Down:=3
: Range("D42,D44,D46,D48,D50").Select
: Range("D50").Activate
: With Selection.Interior
: .ColorIndex = 3
: .Pattern = xlSolid
: .PatternColorIndex = xlAutomatic
End With '*****<--------
: Else
: ActiveWindow.SmallScroll Down:=4
: Range("D42,D44,D46").Select
: Range("D46").Activate
: ActiveWindow.SmallScroll Down:=3
: Range("D42,D44,D46,D48,D50").Select
: Range("D50").Activate
: With Selection.Interior
: .ColorIndex = 3
: .Pattern = xlSolid
: .PatternColorIndex = xlAutomatic
End With '*****<-----------
: End If
: End Sub
:

Hello John,

You are missing the End With statements. I highlighted them in red.

Please Login or Register  to view this content.

Sincerely,
Leith Ross

Don't you need 'End With' after your With blocks?

Put 'End With' before 'Else' and before 'End If.'

I think this does the same as your recorded macro, a bit shorter. You
usually don't have to select cells to do stuff to them.

Sub us()
If Range("M42") = "True" Then
Range("D42,D44,D46,D48,D50").Interior.ColorIndex = 3
Else
Range("D42,D44,D46,D48,D50").Interior.ColorIndex = xlNone
End If
End Sub

HTH
Regards,
Howard

"John" <[email protected]> wrote in message
news:[email protected]...
>I am writing some code in a macro, that when a checkbox is checked, the
>macro
> is run and will look at a cell to see if the word "TRUE" is in it. If it
> is
> true, I want to turn the cell red. If it is not true, I want the cell to
> be
> white. I am getting an else without if error. Below is the code, could
> someone help me?
>
> Sub us()
> '
> ' us Macro
> ' Macro recorded 12/22/2005 by
> '
>
> '
> If Range("M42") = "True" Then
> ActiveWindow.SmallScroll Down:=4
> Range("D42,D44,D46").Select
> Range("D46").Activate
> ActiveWindow.SmallScroll Down:=3
> Range("D42,D44,D46,D48,D50").Select
> Range("D50").Activate
> With Selection.Interior
> .ColorIndex = 3
> .Pattern = xlSolid
> .PatternColorIndex = xlAutomatic
> Else
> ActiveWindow.SmallScroll Down:=4
> Range("D42,D44,D46").Select
> Range("D46").Activate
> ActiveWindow.SmallScroll Down:=3
> Range("D42,D44,D46,D48,D50").Select
> Range("D50").Activate
> With Selection.Interior
> .ColorIndex = 3
> .Pattern = xlSolid
> .PatternColorIndex = xlAutomatic
> End If
> End Sub
>

Just a thought - depending on whether ticking the checkbox triggers a change to the cell (ie whether it is true or false) - you could also just use conditional formatting instead of code.

----

Rob