PDA

View Full Version : Solved: Coloring alternate rows



Codeblue
06-28-2012, 09:07 AM
Hello
This code works pretty well but there are two things I'd hope to fix...

Range("A2:F19").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=MOD( ROW( ), 2) =0"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriori ty
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorLight2
.TintAndShade = 0.799981688894314
End With
Selection.FormatConditions(1).StopIfTrue = True

the first thing is it highlights the selection because of this bit
Range("A2:F19").Select
but it doesn't work without it.
The 2nd item may fix the first and that is I would rather it be dynamic for the number of rows to color. The number of rows will change daily and want it to color where there is data.

xld
06-28-2012, 09:18 AM
With Selection

.FormatConditions.Add Type:=xlExpression, Formula1:="=MOD( ROW( ), 2) =0"
.FormatConditions(.FormatConditions.Count).SetFirstPriority
With .FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorLight2
.TintAndShade = 0.799981688894314
End With
.FormatConditions(1).StopIfTrue = True
End With

CatDaddy
06-28-2012, 09:35 AM
To get from the second cell in A to the last cell in F automatically, add this befor xlds

Range("A2:F" & Range("F" & rows.count).End(xlUp).Row).Select

Codeblue
06-28-2012, 12:30 PM
This didn't color anything.



With Selection


.FormatConditions.Add Type:=xlExpression, Formula1:="=MOD( ROW( ), 2) =0"
.FormatConditions(.FormatConditions.Count).SetFirstPriority
With .FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorLight2
.TintAndShade = 0.799981688894314
End With
.FormatConditions(1).StopIfTrue = True
End With

this helped...



Range("A2:F" & Range("F" & rows.count).End(xlUp).Row).Select

I added this..so it wouldn't highlight the whole area


Range("A1").Select

thanks guys for your input!!

CatDaddy
06-28-2012, 03:57 PM
did you add the select code before xld's code? it would only work if you had something selected...

Codeblue
06-28-2012, 05:26 PM
Yes, it's good now!

lynnnow
06-28-2012, 09:40 PM
Remember to mark your thread Solved if you no longer need assistance on your post.

snb
06-29-2012, 12:21 AM
or

Sub snb()
Cells(2, 5).CurrentRegion.Name = "codeblue"
Range(Join(Filter([transpose(if(mod(row(codeblue),2)=0,"A" & row(codeblue),""))], "A"), ",")).EntireRow.Interior.ColorIndex = 16
End Sub

Codeblue
06-29-2012, 05:42 AM
What is this last code for?

Not sure how to mark this as solved

snb
06-29-2012, 06:36 AM
It's an alternative to get the same result.