Data Filtering and Conditional Formatting 1

[Crystalyzer]

I have data which I have conditionally formatted so that every other line will have a green shade. I used the formula...

=MOD(ROW(),2)

... in the conditional format field, however, when I filter the data the some rows are hidden and some not which causes lines that appear to be one after another having the same shading or non shading. Is there a way to conditionally format the rows so that the displayed rows will alternate the shading properly?

Thanks in advance for any help.

Lloyd

 

[Crystalyzer]

Sorry, forgot to say I am using Excel 2003.

Thanks

 

[fkegley]

I think you're going to need a macro to do this. When the macro runs, it will have to apply the formatting to every other VISIBLE row.

Frank kegley
fkegley@hotmail.com

 

[Crystalyzer]

right you are Frank and here's how I did it for those interested.

Private Sub Worksheet_Calculate()
Range("Start").Select
  j = 1
  k = 0
  For j = 1 To Range("Query_from_Investran").Rows.Count - 1
    If Range("Start").Offset(j, 0).EntireRow.Hidden = False Then
      k = k + 1
      Selection.Offset(1, 0).Select
      If k = 1 Then
      Range(Selection, Selection.Offset(0, Range("Query_from_Investran").Columns.Count - 1)).Select
      End If
      If k Mod 2 = 0 Then
        With Selection.Interior
          .ColorIndex = 35
          .Pattern = xlSolid
        End With
      Else
        Selection.Interior.ColorIndex = xlNone
      End If
    End If
  Next j
  Range("Start").Select
End Sub

Wish I could give myself a star LOL

 

[TriscuiT]

Crystalyzer,

Thanks for posting your solution to your own question. While my needs were not quite the same as yours I was able to find my way based on some of the ideas presented in your code.

Thanks!