shade every other row, automatically

[NYBoy]

Two question:
1. I want my excel spreadsheet to shade every other row automatically?

2. When I use filter, can the excel re-shade every other ro
automatically?

Regards,
NYBo

 

[Max]

From a post by Tom Ogilvy: http://tinyurl.com/dsu87

" If the hidden rows are cause by applying a filter then this formula will
work:

=MOD(SUBTOTAL(3,$A$1:A1),2)=0

Select the rows to format (this assumes the formatting starts with row 1)
Enter the formula as if it were for the the currently active cell (in this
case A1). The range must be applied to a column that will contain values in
each visible cell in the column. "

The formula above will also take care of the normal unfiltered situation

If without filtering, we could try just: =MOD(ROW(),2)=0

 

[v_gyku]

Check this out


Code:
--------------------


Dim objrange As Excel.Range
Dim Counter As Integer
Dim bfound As Boolean
bfound = False
Dim nodata As Integer
nodata = 0

Set objrange = objworksheet.UsedRange.Rows.EntireRow
If objrange.Rows.Count = 1 Then
nodata = nodata + 1

Else


'For every row in the current selection...
For Counter = 1 To objrange.Rows.Count
'If the row is an odd number (within the selection)...
If Counter Mod 2 = 0 Then
'Set the pattern to xlGray16.
objrange.Rows(Counter).Interior.ColorIndex = 15
objrange.Rows(Counter).Interior.Pattern = xlSolid

shaded = True
End If

Next Counter
End If
'Turn error checking back on
On Error GoTo 0

If nodata = 3 Then
shaded = False
End If

--------------------


I think u can undersatnd this code

if shaded=false then dta was not present in sheet
otherwise its ok.

 

[Max]

Note that the preceding formulas are conditional formatting formulas,
applied via Format > Conditional Formatting ..

Select the range (with A1 active)
Click Format > Conditional Formatting
Under Condition 1, make the settings as:
Formula Is | =MOD(SUBTOTAL(3,$A$1:A1),2)=0
Click Format button > Patterns tab > Light green? > OK
Click OK at the main dialog

 

[NYBoy]

You people are the Best!!!

minor correction... A1 should be $A1

=MOD(SUBTOTAL(3,$A$1:*$*A1),2)=0

Thanks
NYBo

 

[Max]

You're welcome !
Glad you were able to tweak to suit ..