coding greenbar

[Joanne]

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]

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]

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

 

[Joanne]

This works really sweet
Thanks

 

[Dave Peterson]

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

 

[Joanne]

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

 

[Joanne]

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.

 

[Dave Peterson]

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]

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]

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.

 

[Dave Peterson]

Stupid fingers:

But be careful.
..range("A1" & LastRow)
may not be what you mean.

(C was supposed to be ctrl-c. I must have hit Shift-C. Doh!)