macro to disallow shading in certain columns
 

i have a spreadsheet that is protected so i have a macro to allow users to
shade or unshade cells. The macro basically says if the cell is no-color,
shade green, if its already green shade it no-color, if its any other color
already, then don't allow to shade over.

now they use it to shade anything they can so i need to limit it to select
columns.
for simplicity, say my spreadsheet is A:Z, they are allowed to shade in
columns F, k, P-T and X-Z. can some one advise me on how to modify my macro?
thanks, Tami


With Selection.Interior
If .ColorIndex = 35 Then
.ColorIndex = xlNone
Else
If .ColorIndex = xlNone Then
.ColorIndex = 35
Else
MsgBox ("Error: One or more of the cells you highlighted cannot
be shaded.")
End If
End If
End With

End Sub

macro to disallow shading in certain columns
 

Maybe something like:

Option Explicit
Sub testme()

Dim myCell As Range
Dim myRng As Range
Dim RngToInspect As Range
Dim wks As Worksheet
Dim FoundAnError As Boolean

Set wks = ActiveSheet

With wks
Set RngToInspect = .Range("F:F,K:K,P:T,x:z")
Set myRng = Nothing
On Error Resume Next
Set myRng = Intersect(Selection, RngToInspect)
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "selection not in authorized range"
Exit Sub
End If

.Unprotect

FoundAnError = False
For Each myCell In myRng.Cells
With myCell.Interior
If .ColorIndex = 35 Then
.ColorIndex = xlNone
Else
If .ColorIndex = xlNone Then
.ColorIndex = 35
Else
FoundAnError = True
End If
End If
End With
Next myCell

.Protect

End With

If FoundAnError = True Then
MsgBox "Error: One or more of the cells you " _
& "highlighted cannot be shaded."
End If

End Sub


Tami wrote:

i have a spreadsheet that is protected so i have a macro to allow users to
shade or unshade cells. The macro basically says if the cell is no-color,
shade green, if its already green shade it no-color, if its any other color
already, then don't allow to shade over.

now they use it to shade anything they can so i need to limit it to select
columns.
for simplicity, say my spreadsheet is A:Z, they are allowed to shade in
columns F, k, P-T and X-Z. can some one advise me on how to modify my macro?
thanks, Tami

With Selection.Interior
If .ColorIndex = 35 Then
.ColorIndex = xlNone
Else
If .ColorIndex = xlNone Then
.ColorIndex = 35
Else
MsgBox ("Error: One or more of the cells you highlighted cannot
be shaded.")
End If
End If
End With

End Sub

--

Dave Peterson

macro to disallow shading in certain columns
 

Dave,
Pure genius! thank you.
happy holidays,
Tami

"Dave Peterson" wrote:

Maybe something like:

Option Explicit
Sub testme()

Dim myCell As Range
Dim myRng As Range
Dim RngToInspect As Range
Dim wks As Worksheet
Dim FoundAnError As Boolean

Set wks = ActiveSheet

With wks
Set RngToInspect = .Range("F:F,K:K,P:T,x:z")
Set myRng = Nothing
On Error Resume Next
Set myRng = Intersect(Selection, RngToInspect)
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "selection not in authorized range"
Exit Sub
End If

.Unprotect

FoundAnError = False
For Each myCell In myRng.Cells
With myCell.Interior
If .ColorIndex = 35 Then
.ColorIndex = xlNone
Else
If .ColorIndex = xlNone Then
.ColorIndex = 35
Else
FoundAnError = True
End If
End If
End With
Next myCell

.Protect

End With

If FoundAnError = True Then
MsgBox "Error: One or more of the cells you " _
& "highlighted cannot be shaded."
End If

End Sub


Tami wrote:

i have a spreadsheet that is protected so i have a macro to allow users to
shade or unshade cells. The macro basically says if the cell is no-color,
shade green, if its already green shade it no-color, if its any other color
already, then don't allow to shade over.

now they use it to shade anything they can so i need to limit it to select
columns.
for simplicity, say my spreadsheet is A:Z, they are allowed to shade in
columns F, k, P-T and X-Z. can some one advise me on how to modify my macro?
thanks, Tami

With Selection.Interior
If .ColorIndex = 35 Then
.ColorIndex = xlNone
Else
If .ColorIndex = xlNone Then
.ColorIndex = 35
Else
MsgBox ("Error: One or more of the cells you highlighted cannot
be shaded.")
End If
End If
End With

End Sub

--

Dave Peterson
.