Cond. Formatting, Mod(Row) and cell conditions

[wynand]

Is there any way to use the mod row function to shade alternative rows, based
on whether cells have any value in them.
I want to select a big range (eg A:G) and only shade alternative rows (A:G)
once data is place in cells.

the mod row function shades regardless if there is data in or not.

 

[Jacob Skaria]

Select ColA:G and try the below CF formula..Make sure the active cell of your
selection is in Row1

=AND(MOD(ROW(),2),COUNTA($A1:$G1))

 

[wynand]

Thanks that works great!
I unfortunately have another problem now:
I have data on sheet 1 that is transferred to a preset LIST in sheet 2 with
VBA.
I also added a sum formula outside of the list which needs to dynamically
move with the list expansion (Total does not work with the code).

If the rows with data on sheet1 are more than that contained in the preset
list on sheet 2, either the sum formula dissapears or cond. formatting with
your formula does not work, as the list is not expanding dynamically or the
shading stops at the old list settings.

Any ideas?