conditional formatting borders

[PhilosophersSage]

I have a spreadsheet that changes size often, and I want to conditionally
format the borders so that if a cell in a row has text the whole row gets
formatted, and the last row needs special formatting seperate from other
rows. Thank you in advance for you help!

 

[Sean Timmons]

Assuming your cell in column A has text in all cases:

highlighting workbook, with cell A2 active, conditional format

=AND($A2<>"",$A3="")

and format with your last row border

2nd format

=$A2<>""

and format with regular border

though, from a personal perspective, seems like quite the reource to use
when you can merely highlight the workbook, remove format, highlight data set
and do formatting as needed, but guess that doesn't matter.

 

[PhilosophersSage]

That is the problem, no one column will always have text, I suppose I could
do a test for each cell, but it seems that when I test each cell not all
cells in row will receive the formatting. What has worked the best so far is :
=IF(OR(ISTEXT(A2),ISTEXT(B2),ISTEXT(C1),ISTEXT(D2),ISTEXT(E2),ISTEXT(F2)),
MOD(ROW(),1)=1,FALSE)

This will get multiple cells in the row, but not all of them

 

[Sean Timmons]

=counta(A2:F2)=0 should do that for you.

That is the problem, no one column will always have text, I suppose I could
do a test for each cell, but it seems that when I test each cell not all
cells in row will receive the formatting. What has worked the best so far is :
=IF(OR(ISTEXT(A2),ISTEXT(B2),ISTEXT(C1),ISTEXT(D2),ISTEXT(E2),ISTEXT(F2)),
MOD(ROW(),1)=1,FALSE)

This will get multiple cells in the row, but not all of them

 

[PhilosophersSage]

That still gives me the format to the last cell that has text, not the whole
row (see example). However it is a much simpler formula. Thank you!

What I get:
| a |
| | b |
| | | c |
| | | | d |
| | b |
| | | | | e |

What I need:
| a | | | | |
| | b | | | |
| | | c | | |
| | | | d | |
| | b | | | |
| | | | | e |

 

[Sean Timmons]

Did you use =counta($A3:$F3)=0 as your first format for final row borders and
=counta($A2:$F2)<>0 as your 2nd format for standard border? If you didn't
put $ in fron, the format will lok in B:G, C:H, etc....

 

[PhilosophersSage]

No I did not, changed it, now it WORKS GREAT! Thank you!

 

[PhilosophersSage]

Oh one more thing: I am useing =AND(COUNTA($A3:$F3)>0,COUNTA($A4:$F4)=0) for
the last line which returns a TRUE on the correct line but the formatting is
not applied. Any suggestions?

 

[Sean Timmons]

Did you put this condition 1st? If not, you'll need to. Once condition 1 is
TRUE, Excel ignores future conditions.

 

[PhilosophersSage]

I switched the order of the conditions and it works compleatly! Thank you!
I played around with my other conditions and the only thing that matters is
that these two are in order. I guess theat stop if true box does not mean
much.

=AND(COUNTA($A3:$E3)>0,COUNTA($A4:$E4)=0)
=COUNTA($A2:$E2)>0