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

Visual Basic Help
 

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

"John" wrote in message
...
: 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
:

Visual Basic Help
 

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

Visual Basic Help
 

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

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

Visual Basic Help
 

Hello John,

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


Code
-------------------

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

-------------------


Sincerely,
Leith Ros

--
Leith Ros
-----------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...fo&userid=1846
View this thread: http://www.excelforum.com/showthread.php?threadid=49569

Visual Basic Help
 

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" wrote in message
...
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

Visual Basic Help
 

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


--
systematic
------------------------------------------------------------------------
systematic's Profile: http://www.excelforum.com/member.php...o&userid=25294
View this thread: http://www.excelforum.com/showthread...hreadid=495691