Select ActiveCell Range

[Tanya]

Hi
I have this checkbox which runs two macros as follows

Private Sub CheckBox1_Click()
If CheckBox1 Then
Yellow
Else
Green
End If
End Sub

Macro 1 is as Follows
Sub Yellow()
'
' Macro1 Macro
'
Range("D4").Select
Range(ActiveCell, ActiveCell.Offset(29, 0)).Select

With Selection.Interior
.ColorIndex = 36
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
End Sub

Question ----------------------------------------------------
How can I in macro1 select those cells immediate below the buttom so that I
can use the same macro with another checkbox?

I also have a password on this worksheet and it appears I want to be able to
unprotect then protect the range at the conclusion. This is confusing for me
because I have two macros [i.e. macro1 and macro2] to consider.

Any assistance would be appreciated.
Regards
Tanya

 

[Tanya]

I want to lock these cells so that they can only be edited if the checkbox is
ticked.
Thanking anyone that can help in advance.
Tanya

 

[Gary Keramidas]

not sure exactly what you're trying to do, but maybe this will at least give an
idea. there is no need to select the cells.

untested
Sub test2()
' Macro1 Macro
If checkbox1.Value = True Then

With Range(Range("D4"), Range("D4").Offset(29))
.Locked = False
.Interior.ColorIndex = 36
.Interior.Pattern = xlSolid
.Interior.PatternColorIndex = xlAutomatic
End With
End If
End Sub



--


Gary

I want to lock these cells so that they can only be edited if the checkbox is
ticked.
Thanking anyone that can help in advance.
Tanya

Hi
I have this checkbox which runs two macros as follows

Private Sub CheckBox1_Click()
If CheckBox1 Then
Yellow
Else
Green
End If
End Sub

Macro 1 is as Follows
Sub Yellow()
'
' Macro1 Macro
'
Range("D4").Select
Range(ActiveCell, ActiveCell.Offset(29, 0)).Select

With Selection.Interior
.ColorIndex = 36
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
End Sub

Question ----------------------------------------------------
How can I in macro1 select those cells immediate below the buttom so that I
can use the same macro with another checkbox?

I also have a password on this worksheet and it appears I want to be able to
unprotect then protect the range at the conclusion. This is confusing for me
because I have two macros [i.e. macro1 and macro2] to consider.

Any assistance would be appreciated.
Regards
Tanya

 

[Tanya]

Hi Gary
I am working on a grade workbook for my classes and would like to highlight
the focus outcomes by column. The workbook is protected and I have since
worked out how to get around that problem with the adjustments below. The
idea is to make the focus outcomes change colour by column so that a teacher
can quickly place the appropriate grade against a student under the relevant
outcome. I have decided upon two colour [macro 'Yellow' and macro 'Green'.
At the same time it would be beneficial if the teacher was unable to select a
cell other than those highlighted, just to prevent further error. Therefore
I am trying to write a command to unlock the cells in question only when the
box has been selected and locked [Macro: 'Yellow'] again when deselected
[Macro: 'Green']

This is as far as I have gotten on this problem to date:
!!! I have just noticed you have added a line [ .Locked = False ] and will
look at this.


Private Sub CheckBox1_Click()

ActiveSheet.Unprotect Password:="BBHS"

If CheckBox1 Then
Yellow
Else
Green
End If

ActiveSheet.Protect Password:="BBHS"
End Sub

Thanks for your prompt feedback.
Regards
Tanya

not sure exactly what you're trying to do, but maybe this will at least give an
idea. there is no need to select the cells.

untested
Sub test2()
' Macro1 Macro
If checkbox1.Value = True Then

With Range(Range("D4"), Range("D4").Offset(29))
.Locked = False
.Interior.ColorIndex = 36
.Interior.Pattern = xlSolid
.Interior.PatternColorIndex = xlAutomatic
End With
End If
End Sub



--


Gary

I want to lock these cells so that they can only be edited if the checkbox is
ticked.
Thanking anyone that can help in advance.
Tanya

Hi
I have this checkbox which runs two macros as follows

Private Sub CheckBox1_Click()
If CheckBox1 Then
Yellow
Else
Green
End If
End Sub

Macro 1 is as Follows
Sub Yellow()
'
' Macro1 Macro
'
Range("D4").Select
Range(ActiveCell, ActiveCell.Offset(29, 0)).Select

With Selection.Interior
.ColorIndex = 36
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
End Sub

Question ----------------------------------------------------
How can I in macro1 select those cells immediate below the buttom so that I
can use the same macro with another checkbox?

I also have a password on this worksheet and it appears I want to be able to
unprotect then protect the range at the conclusion. This is confusing for me
because I have two macros [i.e. macro1 and macro2] to consider.

Any assistance would be appreciated.
Regards
Tanya