I am creating a Template and need a little help.
I have the data entry area set up as a list and have set up the following
conditional format to shade every other row =MOD(SUBTOTAL(3,$A$4:$A4),2)=0
This 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.
Any idea how to correct this so the conditional format formula correctly
carries down for the entire applied area (A:N)? I have been resetting the
conditional format formulas for an hour now to make sure they apply to the
entire data area, but it's not working. I even have 4 starter rows with fake
data to help Excel understand that this needs to carry down, but nothing
works.
Thank you!!!

HI,

Try:

=MOD(ROW(),2)=0

HTH
Jean-Guy

"KC Rippstein" wrote:

I am creating a Template and need a little help.
I have the data entry area set up as a list and have set up the following
conditional format to shade every other row =MOD(SUBTOTAL(3,$A$4:$A4),2)=0
This 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.
Any idea how to correct this so the conditional format formula correctly
carries down for the entire applied area (A:N)? I have been resetting the
conditional format formulas for an hour now to make sure they apply to the
entire data area, but it's not working. I even have 4 starter rows with fake
data to help Excel understand that this needs to carry down, but nothing
works.
Thank you!!!

Can't do that one, as it won't work when a filter is applied. I need to use
the count functionality.
I have even tried to change the reference to column N instead of column A,
since column N actually carries the formatting down, but that also did not
work.
I also tried to do a lookup formula to have it count from $A$4 to the index
of the last cell in column $A with a value, and that did not work at all (it
shaded all rows with data, despite the mod and count functions).

--
Please remember to indicate when the post is answered so others can benefit
from it later.


"pinmaster" wrote:

HI,

Try:

=MOD(ROW(),2)=0

HTH
Jean-Guy

"KC Rippstein" wrote:

I am creating a Template and need a little help.
I have the data entry area set up as a list and have set up the following
conditional format to shade every other row =MOD(SUBTOTAL(3,$A$4:$A4),2)=0
This 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.
Any idea how to correct this so the conditional format formula correctly
carries down for the entire applied area (A:N)? I have been resetting the
conditional format formulas for an hour now to make sure they apply to the
entire data area, but it's not working. I even have 4 starter rows with fake
data to help Excel understand that this needs to carry down, but nothing
works.
Thank you!!!

Hi,

OH a filtered list!!!! I'm not an expert but I don't think that is
possible, not with a function anyway but maybe with VBA, something I can't
help you with sorry about that. ButI'm sure some of the MVP's outhere can
come up with a solution for you, Good Luck!

Regards!
Jean-Guy

"KC Rippstein" wrote:

Can't do that one, as it won't work when a filter is applied. I need to use
the count functionality.
I have even tried to change the reference to column N instead of column A,
since column N actually carries the formatting down, but that also did not
work.
I also tried to do a lookup formula to have it count from $A$4 to the index
of the last cell in column $A with a value, and that did not work at all (it
shaded all rows with data, despite the mod and count functions).

--
Please remember to indicate when the post is answered so others can benefit
from it later.


"pinmaster" wrote:

HI,

Try:

=MOD(ROW(),2)=0

HTH
Jean-Guy

"KC Rippstein" wrote:

I am creating a Template and need a little help.
I have the data entry area set up as a list and have set up the following
conditional format to shade every other row =MOD(SUBTOTAL(3,$A$4:$A4),2)=0
This 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.
Any idea how to correct this so the conditional format formula correctly
carries down for the entire applied area (A:N)? I have been resetting the
conditional format formulas for an hour now to make sure they apply to the
entire data area, but it's not working. I even have 4 starter rows with fake
data to help Excel understand that this needs to carry down, but nothing
works.
Thank you!!!

Actually, this funtion using =MOD(SUBTOTAL(3,$A$4:$A4),2)=0 works
brilliantly, it just won't consistently apply itself to new rows added to the
list, and I am even using Excel's List feature, adding new records where the
blue asterisk is located.

-KC


"pinmaster" wrote:

Hi,

OH a filtered list!!!! I'm not an expert but I don't think that is
possible, not with a function anyway but maybe with VBA, something I can't
help you with sorry about that. ButI'm sure some of the MVP's outhere can
come up with a solution for you, Good Luck!

Regards!
Jean-Guy

"KC Rippstein" wrote:

Can't do that one, as it won't work when a filter is applied. I need to use
the count functionality.
I have even tried to change the reference to column N instead of column A,
since column N actually carries the formatting down, but that also did not
work.
I also tried to do a lookup formula to have it count from $A$4 to the index
of the last cell in column $A with a value, and that did not work at all (it
shaded all rows with data, despite the mod and count functions).

--
Please remember to indicate when the post is answered so others can benefit
from it later.


"pinmaster" wrote:

HI,

Try:

=MOD(ROW(),2)=0

HTH
Jean-Guy

"KC Rippstein" wrote:

I am creating a Template and need a little help.
I have the data entry area set up as a list and have set up the following
conditional format to shade every other row =MOD(SUBTOTAL(3,$A$4:$A4),2)=0
This 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.
Any idea how to correct this so the conditional format formula correctly
carries down for the entire applied area (A:N)? I have been resetting the
conditional format formulas for an hour now to make sure they apply to the
entire data area, but it's not working. I even have 4 starter rows with fake
data to help Excel understand that this needs to carry down, but nothing
works.
Thank you!!!

Hi,

You are absolutetly right, tried it and it works. I preformatted a large
selection using the formula below so that any added data will already have
the conditional formatting, I think that is what you need to do:

=AND(MOD(SUBTOTAL(3,A2:A$2),2)=0,$A2<"")

not sure though what you mean by excel list feature and blue asterix. You
must have a newer version than me.


HTH
Jean-Guy

"KC Rippstein" wrote:

Actually, this funtion using =MOD(SUBTOTAL(3,$A$4:$A4),2)=0 works
brilliantly, it just won't consistently apply itself to new rows added to the
list, and I am even using Excel's List feature, adding new records where the
blue asterisk is located.

-KC


"pinmaster" wrote:

Hi,

OH a filtered list!!!! I'm not an expert but I don't think that is
possible, not with a function anyway but maybe with VBA, something I can't
help you with sorry about that. ButI'm sure some of the MVP's outhere can
come up with a solution for you, Good Luck!

Regards!
Jean-Guy

"KC Rippstein" wrote:

Can't do that one, as it won't work when a filter is applied. I need to use
the count functionality.
I have even tried to change the reference to column N instead of column A,
since column N actually carries the formatting down, but that also did not
work.
I also tried to do a lookup formula to have it count from $A$4 to the index
of the last cell in column $A with a value, and that did not work at all (it
shaded all rows with data, despite the mod and count functions).

--
Please remember to indicate when the post is answered so others can benefit
from it later.


"pinmaster" wrote:

HI,

Try:

=MOD(ROW(),2)=0

HTH
Jean-Guy

"KC Rippstein" wrote:

I am creating a Template and need a little help.
I have the data entry area set up as a list and have set up the following
conditional format to shade every other row =MOD(SUBTOTAL(3,$A$4:$A4),2)=0
This 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.
Any idea how to correct this so the conditional format formula correctly
carries down for the entire applied area (A:N)? I have been resetting the
conditional format formulas for an hour now to make sure they apply to the
entire data area, but it's not working. I even have 4 starter rows with fake
data to help Excel understand that this needs to carry down, but nothing
works.
Thank you!!!