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)
>>> >
>>
>
>

You could also use a custom format of ;;; (3 semicolons).

And if you could pick out a column that always has data when that row is used,
you could use something like:

Option Explicit
Sub testme()
Dim myRng As Range
Dim LastRow As Long

With Worksheets("Sheet1")
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

With .Range("a1:x" & LastRow)
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, _
Formula1:="=AND($A$1<>"""",MOD(ROW(),2)=1)"
.FormatConditions(1).Interior.ColorIndex = 35
End With
End With
End Sub

I used column A as my indicator column and formatted A:X (and 35 is light green
in my workbook).



Joanne wrote:
>
> 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)
> >>> >
> >>
> >
> >

--

Dave Peterson

Thanks for the code Dave.
Couple questions please

what does A:X do for the routine?
I understand .range("A1" & LastRow), but again, what is the :x added
in there for?

Also, as I understand the code when reading it, it will apply to the
entire worksheet. My users need to apply it to 3 or 4 different ranges
of the same worksheet, so I need to know how to code it using a range
'name' that will allow expansion and deletion of rows or columns (at
least this is how I understand what I read in the help files regarding
the naming of ranges and it's usefullness)

Dave Peterson wrote:

>You could also use a custom format of ;;; (3 semicolons).
>
>And if you could pick out a column that always has data when that row is used,
>you could use something like:
>
>Option Explicit
>Sub testme()
> Dim myRng As Range
> Dim LastRow As Long
>
> With Worksheets("Sheet1")
> LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
>
> With .Range("a1:x" & LastRow)
> .FormatConditions.Delete
> .FormatConditions.Add Type:=xlExpression, _
> Formula1:="=AND($A$1<>"""",MOD(ROW(),2)=1)"
> .FormatConditions(1).Interior.ColorIndex = 35
> End With
> End With
>End Sub
>
>I used column A as my indicator column and formatted A:X (and 35 is light green
>in my workbook).
>
>
>
>Joanne wrote:
>>
>> 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)
>> >>> >
>> >>
>> >
>> >
>

I thought that along with your requirement to only conditionally format certain
rows, you'd want to only conditionally format certain columns. And in my code,
I was only working on columns A:X.

But be careful.
C
may not be what you mean.

If lastrow is 234 (say), then this
..range("A1" & LastRow)
would be equivalent to:
..range("A1" & 234)
or
..range("A1234")
(Just that one cell)

Debra Dalgleish shows how to use a dynamic range here:
http://www.contextures.com/xlNames01.html#Dynamic

You'll have to adjust the formula to only look at the cells that are included in
each of the 4 ranges. Debra's sample name looks at all of column A.





Joanne wrote:
>
> Thanks for the code Dave.
> Couple questions please
>
> what does A:X do for the routine?
> I understand .range("A1" & LastRow), but again, what is the :x added
> in there for?
>
> Also, as I understand the code when reading it, it will apply to the
> entire worksheet. My users need to apply it to 3 or 4 different ranges
> of the same worksheet, so I need to know how to code it using a range
> 'name' that will allow expansion and deletion of rows or columns (at
> least this is how I understand what I read in the help files regarding
> the naming of ranges and it's usefullness)
>
> Dave Peterson wrote:
>
> >You could also use a custom format of ;;; (3 semicolons).
> >
> >And if you could pick out a column that always has data when that row is used,
> >you could use something like:
> >
> >Option Explicit
> >Sub testme()
> > Dim myRng As Range
> > Dim LastRow As Long
> >
> > With Worksheets("Sheet1")
> > LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
> >
> > With .Range("a1:x" & LastRow)
> > .FormatConditions.Delete
> > .FormatConditions.Add Type:=xlExpression, _
> > Formula1:="=AND($A$1<>"""",MOD(ROW(),2)=1)"
> > .FormatConditions(1).Interior.ColorIndex = 35
> > End With
> > End With
> >End Sub
> >
> >I used column A as my indicator column and formatted A:X (and 35 is light green
> >in my workbook).
> >
> >
> >
> >Joanne wrote:
> >>
> >> 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)
> >> >>> >
> >> >>
> >> >
> >> >
> >

--

Dave Peterson