Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
conditional formatting borders
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
conditional formatting borders
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" wrote: 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! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
conditional formatting borders
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" wrote: 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" wrote: 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! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
conditional formatting borders
=counta(A2:F2)=0 should do that for you.
"PhilosophersSage" wrote: 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" wrote: 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" wrote: 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! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
conditional formatting borders
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" wrote: =counta(A2:F2)=0 should do that for you. "PhilosophersSage" wrote: 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" wrote: 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" wrote: 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! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
conditional formatting borders
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" wrote: 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" wrote: =counta(A2:F2)=0 should do that for you. "PhilosophersSage" wrote: 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" wrote: 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" wrote: 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! |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
conditional formatting borders
No I did not, changed it, now it WORKS GREAT! Thank you!
"Sean Timmons" wrote: 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" wrote: 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" wrote: =counta(A2:F2)=0 should do that for you. "PhilosophersSage" wrote: 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" wrote: 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" wrote: 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! |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
conditional formatting borders
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? "PhilosophersSage" wrote: No I did not, changed it, now it WORKS GREAT! Thank you! "Sean Timmons" wrote: 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" wrote: 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" wrote: =counta(A2:F2)=0 should do that for you. "PhilosophersSage" wrote: 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" wrote: 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" wrote: 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! |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
conditional formatting borders
Did you put this condition 1st? If not, you'll need to. Once condition 1 is
TRUE, Excel ignores future conditions. "PhilosophersSage" wrote: 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? "PhilosophersSage" wrote: No I did not, changed it, now it WORKS GREAT! Thank you! "Sean Timmons" wrote: 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" wrote: 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" wrote: =counta(A2:F2)=0 should do that for you. "PhilosophersSage" wrote: 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" wrote: 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" wrote: 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! |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
conditional formatting borders
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 "Sean Timmons" wrote: Did you put this condition 1st? If not, you'll need to. Once condition 1 is TRUE, Excel ignores future conditions. "PhilosophersSage" wrote: 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? "PhilosophersSage" wrote: No I did not, changed it, now it WORKS GREAT! Thank you! "Sean Timmons" wrote: 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" wrote: 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" wrote: =counta(A2:F2)=0 should do that for you. "PhilosophersSage" wrote: 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" wrote: 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" wrote: 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formatting Borders | Excel Discussion (Misc queries) | |||
Creating borders via Conditional Formatting | Excel Discussion (Misc queries) | |||
Conditional Borders? | Excel Discussion (Misc queries) | |||
Conditional Formatting of merged cells, borders don't show correct | Excel Discussion (Misc queries) | |||
Merged Cell Conditional Formatting Borders | Excel Discussion (Misc queries) |