Need help with Macro's and Shading

[Madhart]

Sorry If I'm posting in the wrong place.

I'm trying to set up some shading macro's or rules for an excel page (excel
2003) and running into a little difficulty. I set up a macro to put in a
light shading to make everything easy on the eyes every even number row:
Range( _
"A8:S8,[through],A94:S94" _
).Select
With Selection.Interior
.ColorIndex = 24
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Range("G4:M4").Select
End Sub
I also created one to remove the shading.

however, I also have a macro to add a mandatory and needed shading of:
With Selection.Interior
.ColorIndex = 2
.Pattern = xlLightUp
.PatternColorIndex = xlAutomatic
End With
End Sub
This shading will be in random cells and change day by day but needs to stay.

My question is: Is there any way to create an exception so that if there
are any cells that have the .ColorIndex = 2/.Pattern = xlLightUp shading in
them then the other shadings/or removal won't overwrite them but instead
leave them and continue filling in the rest of the cells?

Thanks

 

[Gary''s Student]

Very easy. When you apply the mandatory format, save the range. Then you
can re-apply the format whenever you want:

With Selection.Interior
.ColorIndex = 2
.Pattern = xlLightUp
.PatternColorIndex = xlAutomatic
End With
Set mandatory = Selection

 

[macropod]

Hi Madhart,

Try this in the relevant worksheet module:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Line As Range
Dim oCel As Range
With Target
If .Row > 7 And .Row < 95 And .Column < 19 Then
Set Line = Range(Cells(.Row, 1), Cells(.Row, 19))
For Each oCel In Line.Cells
With oCel.Interior
If .ColorIndex <> 2 Then
If .Row Mod 2 = 0 Then
.ColorIndex = 24
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
Else
.ColorIndex = 22
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End If
End If
End With
Next
End If
End With
End Sub

You might want to change .ColorIndex = 22 to some other value.

 

[Madhart]

First Thank you for taking the time to help.

I'm not fully sure what you meant. I tried to copy and past your reply in
to the macro and it didn't work. The others still over wrote it. Problem is
I won't be the only one using this. It will be used at work and some of the
people there aren't all that great with computers so it will need to be
simple. I figure pushing a few keys to highlight the areas needed will be
simple enough but if it takes out the the other highlighted areas that are
actually needed, it will throw them off. If you don't mind expanding on what
said that would be great. Do I need to just copy and paste that into my
macro formula or are there other values or changes that I need to make as
well? Thanks

Very easy. When you apply the mandatory format, save the range. Then you
can re-apply the format whenever you want:

With Selection.Interior
.ColorIndex = 2
.Pattern = xlLightUp
.PatternColorIndex = xlAutomatic
End With
Set mandatory = Selection

--
Gary''s Student - gsnu200800

Sorry If I'm posting in the wrong place.

I'm trying to set up some shading macro's or rules for an excel page (excel
2003) and running into a little difficulty. I set up a macro to put in a
light shading to make everything easy on the eyes every even number row:
Range( _
"A8:S8,[through],A94:S94" _
).Select
With Selection.Interior
.ColorIndex = 24
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Range("G4:M4").Select
End Sub
I also created one to remove the shading.

however, I also have a macro to add a mandatory and needed shading of:
With Selection.Interior
.ColorIndex = 2
.Pattern = xlLightUp
.PatternColorIndex = xlAutomatic
End With
End Sub
This shading will be in random cells and change day by day but needs to stay.

My question is: Is there any way to create an exception so that if there
are any cells that have the .ColorIndex = 2/.Pattern = xlLightUp shading in
them then the other shadings/or removal won't overwrite them but instead
leave them and continue filling in the rest of the cells?

Thanks

 

[Madhart]

Thanks macropod.

I tried to copy and paste that in and try it but I got a compile error:
Expected End Sub. I'm sure what that means but do I need to add in some
other values or info to that formula? Thanks for your help.

Hi Madhart,

Try this in the relevant worksheet module:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Line As Range
Dim oCel As Range
With Target
If .Row > 7 And .Row < 95 And .Column < 19 Then
Set Line = Range(Cells(.Row, 1), Cells(.Row, 19))
For Each oCel In Line.Cells
With oCel.Interior
If .ColorIndex <> 2 Then
If .Row Mod 2 = 0 Then
.ColorIndex = 24
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
Else
.ColorIndex = 22
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End If
End If
End With
Next
End If
End With
End Sub

You might want to change .ColorIndex = 22 to some other value.

--
Cheers
macropod
[MVP - Microsoft Word]

Sorry If I'm posting in the wrong place.

I'm trying to set up some shading macro's or rules for an excel page (excel
2003) and running into a little difficulty. I set up a macro to put in a
light shading to make everything easy on the eyes every even number row:
Range( _
"A8:S8,[through],A94:S94" _
).Select
With Selection.Interior
.ColorIndex = 24
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Range("G4:M4").Select
End Sub
I also created one to remove the shading.

however, I also have a macro to add a mandatory and needed shading of:
With Selection.Interior
.ColorIndex = 2
.Pattern = xlLightUp
.PatternColorIndex = xlAutomatic
End With
End Sub
This shading will be in random cells and change day by day but needs to stay.

My question is: Is there any way to create an exception so that if there
are any cells that have the .ColorIndex = 2/.Pattern = xlLightUp shading in
them then the other shadings/or removal won't overwrite them but instead
leave them and continue filling in the rest of the cells?

Thanks

 

[Bob Phillips]

Public Sub AddShade()
Dim flip As Boolean
Dim cell As Range

For Each cell In Selection.Columns(1).Cells

With cell

If .Interior.ColorIndex = 2 And .Interior.Pattern = xlLightUp
Then

'do nothing
Else

If flip Then

.EntireRow.Interior.ColorIndex = 22
End If
flip = Not flip
End If
End With
Next cell
End Sub

 

[macropod]

Hi Madhart,

I can't reproduce that error, but I did find a different one. Change:
If .Row Mod 2 = 0 Then
to:
If Target.Row Mod 2 = 0 Then

Note: If your shading is supposed to apply to the odd rows, then change
If Target.Row Mod 2 = 0 Then
to
If Target.Row Mod 2 = 1 Then

--
Cheers
macropod
[MVP - Microsoft Word]

Thanks macropod.

I tried to copy and paste that in and try it but I got a compile error:
Expected End Sub. I'm sure what that means but do I need to add in some
other values or info to that formula? Thanks for your help.

Hi Madhart,

Try this in the relevant worksheet module:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Line As Range
Dim oCel As Range
With Target
If .Row > 7 And .Row < 95 And .Column < 19 Then
Set Line = Range(Cells(.Row, 1), Cells(.Row, 19))
For Each oCel In Line.Cells
With oCel.Interior
If .ColorIndex <> 2 Then
If .Row Mod 2 = 0 Then
.ColorIndex = 24
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
Else
.ColorIndex = 22
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End If
End If
End With
Next
End If
End With
End Sub

You might want to change .ColorIndex = 22 to some other value.

--
Cheers
macropod
[MVP - Microsoft Word]

Sorry If I'm posting in the wrong place.

I'm trying to set up some shading macro's or rules for an excel page (excel
2003) and running into a little difficulty. I set up a macro to put in a
light shading to make everything easy on the eyes every even number row:
Range( _
"A8:S8,[through],A94:S94" _
).Select
With Selection.Interior
.ColorIndex = 24
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Range("G4:M4").Select
End Sub
I also created one to remove the shading.

however, I also have a macro to add a mandatory and needed shading of:
With Selection.Interior
.ColorIndex = 2
.Pattern = xlLightUp
.PatternColorIndex = xlAutomatic
End With
End Sub
This shading will be in random cells and change day by day but needs to stay.

My question is: Is there any way to create an exception so that if there
are any cells that have the .ColorIndex = 2/.Pattern = xlLightUp shading in
them then the other shadings/or removal won't overwrite them but instead
leave them and continue filling in the rest of the cells?

Thanks

 

[gls858]

Sorry If I'm posting in the wrong place.

I'm trying to set up some shading macro's or rules for an excel page (excel
2003) and running into a little difficulty. I set up a macro to put in a
light shading to make everything easy on the eyes every even number row:
Range( _
"A8:S8,[through],A94:S94" _
).Select
With Selection.Interior
.ColorIndex = 24
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Range("G4:M4").Select
End Sub
I also created one to remove the shading.

however, I also have a macro to add a mandatory and needed shading of:
With Selection.Interior
.ColorIndex = 2
.Pattern = xlLightUp
.PatternColorIndex = xlAutomatic
End With
End Sub
This shading will be in random cells and change day by day but needs to stay.

My question is: Is there any way to create an exception so that if there
are any cells that have the .ColorIndex = 2/.Pattern = xlLightUp shading in
them then the other shadings/or removal won't overwrite them but instead
leave them and continue filling in the rest of the cells?

Thanks

Not sure if this will help or not but I've found this freebie very
helpful. One of the functions it has it to shade every nth line.

http://www.asap-utilities.com/

Standard disclaimer: not associated with or receive money from yada yada

gls858