conditional formatting: alternate rules on a condition

[Berrett]

I am using:

=MOD(ROW(),2)=0

... to alternate row colors in a spreadsheet. However, what I'd really like to do is have the row columns alternate colors IF there is a date in row 6. If there is no date in row 6 I'd like all of the rows to just be a light gray, or maybe just have no formatting at all.

Can this be done?

BTW: Row 6 is not included in the conditionally formatted rows. The conditionally formatted rows 9-109.

 

[T. Valko]

I am using:

=MOD(ROW(),2)=0

... to alternate row colors in a spreadsheet. However, what I'd really like to do is have the row columns alternate colors IF there is a date in row 6. If there is no date in row 6 I'd like all of the rows to just be a light gray, or maybe just have no formatting at all.

Can this be done?

BTW: Row 6 is not included in the conditionally formatted rows. The conditionally formatted rows 9-109.

Where in row 6?

Just add a test to your current formula.

=AND(COUNT($A$6),MOD(ROW(),2)=0)

 

[Berrett]

I have not tried your solution yet, but you asked where Row 6 was. It looks like this:

http://www.flickr.com/photos/61932465@N00/5808357937/

I want conditional formatting on the columns that have a date in row 6, and nothing in the columns that have nothing in row 6. (Actually, there's a formula in row 6 -- so it's not blank). I hope that helps. I will check out your solution now.

 

[Berrett]

Maybe I'm misapplying it, but I can't get that solution to work. I tried it on a single column (changing a6 to m6), and I tried it on a group of columns. But no luck. Now with the picture to clarify the situation, is there a better solution?

 

[T. Valko]

Maybe I'm misapplying it, but I can't get that solution to work. I tried it on a single column (changing a6 to m6), and I tried it on a group of columns. But no luck. Now with the picture to clarify the situation, is there a better solution?

What version of Excel are you using?

 

[Berrett]

Excel for Mac 2008

 

[T. Valko]

Excel for Mac 2008

Well, I have no idea if the Apple version of Excel is the same or similar to what version of Excel for PCs.

Maybe someone that also uses an Apple will pass by.

 

[jasmith4]

The condition should not have the dollar-sign in front of the A:

=AND(COUNTA(A$6)>0,MOD(ROW(),2)=0)
​

because you want it to ask about row 6 in the current column, not row 6 in column A. I also changed to the COUNTA function, which more reliably distinguishes blank from non-blank, and added ">0" to return a Boolean rather than an integer.

This should be the condition only in column A -- in column B it should be:

=AND(COUNTA(B$6)>0,MOD(ROW(),2)=0)
​

etc.

 

[T. Valko]

The condition should not have the dollar-sign in front of the A:

=AND(COUNTA(A$6)>0,MOD(ROW(),2)=0)
​

because you want it to ask about row 6 in the current column, not row 6 in column A. I also changed to the COUNTA function, which more reliably distinguishes blank from non-blank, and added ">0" to return a Boolean rather than an integer.

This should be the condition only in column A -- in column B it should be:

=AND(COUNTA(B$6)>0,MOD(ROW(),2)=0)
​

etc.

COUNTA is not a very robust test for a date!

COUNT or ISNUMBER would be more robust.

 

[Berrett]

I got a solution that worked - Thank you very much to both of you!

Because row 6 is never truly blank (it has a formula in it), the counta and the ">0" did not work. However, when I modified it to look like this:

=AND(NOT(I$6=""),MOD(ROW(),2)=0)

... it worked. I'm not smart enough to know what the difference is or why this was successful, but it's working. Thanks a lot. I would have never found this solution without your input.