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


Dave Peterson wrote:

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

--

Dave Peterson