Sub vv()
With Intersect(Range("A1:A" & Cells(Rows.Count, 1).End(3).Row).EntireRow, ActiveSheet.UsedRange)
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=MOD(SUBTOTAL(3,$A$1:$A1),2)=0"
.FormatConditions(1).Interior.ColorIndex = 37
End With
End Sub
ADVERTISEMENT
The row numbers are irrelevant.Thanks footoo but this will not work, since the row numbers are not a natural serie 1,2,3,4,5 etc.
That is what the code does based on column A.All I still want to do is to better define the range --> from the top row until the last row.
Sub v()
Dim rng As Range, lc&, x&, cel As Range
Set rng = Range("A2:A" & Cells(Rows.Count, 1).End(3).Row).SpecialCells(xlCellTypeVisible)
Cells.Interior.ColorIndex = xlNone
lc = Cells(1, Columns.Count).End(xlToLeft).Column
x = 1
For Each cel In rng
If x Mod 2 = 0 Then Range(cel, Cells(cel.Row, lc)).Interior.ColorIndex = 37
x = x + 1
Next
End Sub