Conditional Formating based on # of cells with certain color.

[Mlogs]

Hi,

I have this conditional formatting rule:
=AND($W2<>0,$W2<>"")
It is applying a light green fill color when the conditions are met.

What I would like to do is have every group of 14 rows alternate between this color and a slightly different color. Is it possible to setup another conditional formatting rule to achieve this?

Thank you.

 

[Joe4]

You can do a calculation that returns the row number, divides by 14, and determines whether or not the Integer portion of that value is even or odd (so that value would change every 14 rows).
So, it might look something like this:

[COLOR=#333333]=AND($W2<>0,$W2<>"",[/COLOR]ISODD(INT((ROW()-2)/14))[COLOR=#333333])[/COLOR]

for one color, and this:

[COLOR=#333333]=AND($W2<>0,$W2<>"",[/COLOR]ISEVEN(INT((ROW()-2)/14))[COLOR=#333333])[/COLOR]

for the other.

 

[Mlogs]

Hi Joe,

This would work if all the rows were continuous. They are not, there are rows with no color inbetween colored rows. The conditional formula would need to be able to count the number of rows and apply the color based on a count. Does this mean is would need to be a vba written code to work? I do know a fair bit of coding, but I am not sure how to make it constantly look for values that update.

 

[Joe4]

Does this mean is would need to be a vba written code to work?

Most likely.

I am not sure how to make it constantly look for values that update.

It really depends on how they are being updated.
There are event procedures which run VBA code automatically based on certain events happening.
Worksheet_Change can be used with manual updates.
Update via formulas or links would probably require a Worksheet_Calculate event procedure.

 

[jim may]

You can do a calculation that returns the row number, divides by 14, and determines whether or not the Integer portion of that value is even or odd (so that value would change every 14 rows).
So, it might look something like this:

[COLOR=#333333]=AND($W2<>0,$W2<>"",[/COLOR]ISODD(INT((ROW()-2)/14))[COLOR=#333333])[/COLOR]

for one color, and this:

[COLOR=#333333]=AND($W2<>0,$W2<>"",[/COLOR]ISEVEN(INT((ROW()-2)/14))[COLOR=#333333])[/COLOR]

for the other.

Joe,

What is the "-2" of the ROW()-2 doing here?

Jim

 

[Joe4]

What is the "-2" of the ROW()-2 doing here?

Look at his original formula:

=AND($W2<>0,$W2<>"")

From that, I figured they were starting on row 2. So, the first colored band should be rows 2-15, then 16-29, etc.
In order to get that function to calculate properly, you need to "offset" it by 2.

To see this more easily, enter this formula in any column in row 2, and copy down a bunch of rows:

=ISODD(INT((ROW()-2)/14)

and notice at which rows in changes values from TRUE to FALSE.

 

[jim may]

Got it!! Thanks