Visual Basic Help

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
You need to close your With sections with End With. Note inserted lines
below
Paul D

: 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
:
 
Don't you need 'End With' after your With blocks?

Put 'End With' before 'Else' and before 'End If.'
 
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
 
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
 
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.
 
Back
Top