When I use conditional formating to get the greenbar effect on my
spreadsheet it wants to print that way and I really only want it to be
on my screen for working in the spreadsheet. I want it to print with
no colors or patterns.

I can't figure out how to do that with the conditional formating so I
was thinking using vba would be the best thing to do.

I found this bit of code at MrExcel.Com while surfing for the answer
to my dilemma. This is run as a macro, needing to push a button (or
vba/run) to apply it to your selection. But I don't see anywhere that
it is limited to be visible on screen only. Could someone please help
me adapt the code to meet my criteria?

Sub ApplyGreenBarToSelection()
n = 0
For Each VisRow In Selection.Resize(,
1).SpecialCells(xlCellTypeVisible)
n = n + 1
If n Mod 2 = 0 Then
VisRow.EntireRow.Interior.ColorIndex = 35
End If
Next VisRow
End Sub

Thank You as always
Joanne

How about an alternative that uses the format|conditional formatting plus
another cell.

Pick out a cell that you can use as an indicator (I used $A$1).

Then the conditional formatting formula could check that cell, too:

=AND($A$1<"",MOD(ROW(),2)=1)

If you put something (anything!) in A1, then you see the banding. Right before
you print, you select A1 and hit the delete key on the keyboard and the banding
disappears.



Joanne wrote:

When I use conditional formating to get the greenbar effect on my
spreadsheet it wants to print that way and I really only want it to be
on my screen for working in the spreadsheet. I want it to print with
no colors or patterns.

I can't figure out how to do that with the conditional formating so I
was thinking using vba would be the best thing to do.

I found this bit of code at MrExcel.Com while surfing for the answer
to my dilemma. This is run as a macro, needing to push a button (or
vba/run) to apply it to your selection. But I don't see anywhere that
it is limited to be visible on screen only. Could someone please help
me adapt the code to meet my criteria?

Sub ApplyGreenBarToSelection()
n = 0
For Each VisRow In Selection.Resize(,
1).SpecialCells(xlCellTypeVisible)
n = n + 1
If n Mod 2 = 0 Then
VisRow.EntireRow.Interior.ColorIndex = 35
End If
Next VisRow
End Sub

Thank You as always
Joanne

--

Dave Peterson

That's an interesting approach to the problem, and I certainly will
give it a try.
My question is, do I need to reset the conditional programming each
time I do this as a result of deleting it in A1?
Thanks for your interest in my dilemma
Joanne
Dave Peterson wrote:


When I use conditional formating to get the greenbar effect on my
spreadsheet it wants to print that way and I really only want it to be
on my screen for working in the spreadsheet. I want it to print with
no colors or patterns.

I can't figure out how to do that with the conditional formating so I
was thinking using vba would be the best thing to do.

I found this bit of code at MrExcel.Com while surfing for the answer

This works really sweet
Thanks
Dave Peterson wrote:

=AND($A$1<"",MOD(ROW(),2)=1)

Glad you got it working and you found out that you only had to toggle the value
in that one cell.

Joanne wrote:

This works really sweet
Thanks
Dave Peterson wrote:

=AND($A$1<"",MOD(ROW(),2)=1)


--

Dave Peterson

Dave
This is how I have used your cond format code

=AND($A$1<"",MOD(ROW(),2)=1)

Then, On and Off buttons on toolbar for user with this code behind
them:
On Button
Public Sub FillCell()
With Worksheets(1).range("A1")
.Value = "On"
End With
End Sub

Off Button
Public Sub ClearContents()
Dim range
Worksheets("tblMain").range("A1").ClearContents
End Sub

And this all works great, but of course ;-), I want more!!

I am wondering if I can do the conditional formatting by code instead
so that I can name the range to apply it to - then if my user adds or
deletes cols or rows, this little trick will still work. If so, how do
I do it.

I am also wondering if the value in the indicator cell can be set to
visible=false in the code and yet be able to do the job. That way the
user never needs to see it toggle on and off, as I will be setting
this for several ranges on the same worksheet. Just would look better
I think if it can be invisible.

Warned you that I want more - I'm a 'would be' coding junkie with
woefully little skills!

Thanks for your interest in my little project
Joanne

Dave Peterson wrote:

Glad you got it working and you found out that you only had to toggle the value
in that one cell.

Joanne wrote:

This works really sweet
Thanks
Dave Peterson wrote:

=AND($A$1<"",MOD(ROW(),2)=1)

Duh
I just figured out that the value I put in the indicator cell could be
a space and I then have nothing showing. Works really sweet.

Joanne wrote:

Dave
This is how I have used your cond format code

=AND($A$1<"",MOD(ROW(),2)=1)

Then, On and Off buttons on toolbar for user with this code behind
them:
On Button
Public Sub FillCell()
With Worksheets(1).range("A1")
.Value = "On"
End With
End Sub

Off Button
Public Sub ClearContents()
Dim range
Worksheets("tblMain").range("A1").ClearContents
End Sub

And this all works great, but of course ;-), I want more!!

I am wondering if I can do the conditional formatting by code instead
so that I can name the range to apply it to - then if my user adds or
deletes cols or rows, this little trick will still work. If so, how do
I do it.

I am also wondering if the value in the indicator cell can be set to
visible=false in the code and yet be able to do the job. That way the
user never needs to see it toggle on and off, as I will be setting
this for several ranges on the same worksheet. Just would look better
I think if it can be invisible.

Warned you that I want more - I'm a 'would be' coding junkie with
woefully little skills!

Thanks for your interest in my little project
Joanne

Dave Peterson wrote:

Glad you got it working and you found out that you only had to toggle the value
in that one cell.

Joanne wrote:

This works really sweet
Thanks
Dave Peterson wrote:

=AND($A$1<"",MOD(ROW(),2)=1)