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.

Bob Phillips
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.