Conditional Format in a List

[KC Rippstein]

I am creating a Template and need a little help with automatic row shading.I have the data entry area set up as a list with a total row and have set up the following conditional format to shade every other row =MOD(SUBTOTAL(3,$A$4:$A4),2)=0This conditional format is only applied to the data area (columns A through N), not to the entire row. When I go to the blue asterisk and enter 2 new records, the conditional format only carries down in columns M and N.Those two columns have formulas...columns A through L are data entry. So I tried to switch the formula to look at column N, but that did not change anything.I even have 4 starter rows with fake data to help Excel understand that this needs to carry down, but nothing works. I also tried to use =MOD(SUBTOTAL(3,$A$4:INDEX($A$4:$A$2000,LOOKUP(2,1/($A$4:$A$2000),$A$4:$A$2000))),2)=0 which did not work...it shaded nothing. I also tried to name the range A4:A7 (the last cell currently with data, which will allow the named range to dynamically change as records are added to the list) and then use the named range in that formula...that shaded all rows.Any ideas how to fix this??Thank you!!!

 

[Bob Phillips]

It seems to work for me. What are the formulae in M and N?

Blue asterisk - what is that?

--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"KC Rippstein" <kcrippsteinAThotmailDOTcom> wrote in message
I am creating a Template and need a little help with automatic row shading.I
have the data entry area set up as a list with a total row and have set up
the following conditional format to shade every other row
=MOD(SUBTOTAL(3,$A$4:$A4),2)=0This conditional format is only applied to the
data area (columns A through N), not to the entire row. When I go to the
blue asterisk and enter 2 new records, the conditional format only carries
down in columns M and N.Those two columns have formulas...columns A through
L are data entry. So I tried to switch the formula to look at column N, but
that did not change anything.I even have 4 starter rows with fake data to
help Excel understand that this needs to carry down, but nothing works. I
also tried to use
=MOD(SUBTOTAL(3,$A$4:INDEX($A$4:$A$2000,LOOKUP(2,1/($A$4:$A$2000),$A$4:$A$20
00))),2)=0 which did not work...it shaded nothing. I also tried to name the
range A4:A7 (the last cell currently with data, which will allow the named
range to dynamically change as records are added to the list) and then use
the named range in that formula...that shaded all rows.Any ideas how to fix
this??Thank you!!!

 

[KC Rippstein]

I am using Excel 2003, which has a new List feature. It adds a blue
asterisk at the place a new record or records should be added, and it
automatically applies all formatting and formulas from the rows above to the
new records. It also has a totals row that uses the subtotal function for
any number of operations you want.
The problem is my formats are not all carrying down to the new records like
they should. The formula works great, but when I add new records, I have to
select my entire range, go to conditional format, see that my formula is
still there, and hit enter. Then Excel comes to its senses and actually
performs the formatting it says is active.
I may try preformatting 2,000 rows like this as a work-around, but we'll see
if I can convince the total row to retain its own format as it keeps moving
down the sheet.
If that fails, I'll have to do a worksheet change event macro off colomn A
to select my entire dataset and open and close conditional format.
Thanks for your response.
-KC