# Excel Macro's for format shading



## Madhart (Aug 17, 2008)

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 and everyeven number row:
Range( _
"A8:S8,A10:S10,A12:S12,[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. I also have some printing macros that I hope will bennifit from your answers. Right now the macro's overwrite each other.

My question is: Is there any way to create an exception or something 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


----------



## DataBase (Jan 15, 2004)

not sure if this helps, but i have added your code to the Sheet_Change method which means that when ever something changes this will be activated, also when i ran your code it seemed that the highlighted lines just continued without clearing the previous so heres an attempt to rectify that. i just added two lines to clear the colourindex off the entire sheet then start the highlighting again. 

try this first then let uskno ehat else is needed and we will try and help.

Private Sub Worksheet_Change(ByVal Target As Range)
Sheet1.Cells.Select
Selection.Interior.ColorIndex = 0

Sheet1.Range("A8:S8,A10:S10,A12:S12,A94:S94").Select
With Selection.Interior
.ColorIndex = 24
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Range("G4:M4").Select
End Sub


----------

