|
Format bases on IF Statement
Hello,
I created a subtotal using excel's Subtotal option. Example, DIV NAME2 NAME3 NAME4 QTY Ext TEST1 JX JY JQ 42 52 TEST1 Total 42 52 TEST2 JX JY JQ 54 60 TEST2 Total 54 60 So I want to format Col A,B,C and D only if Row equals TEST1 Total. So something like thits If A:A="TEST Total" Or "TEST2 Total" Then format Col A,B,C D only for that row. Etc.... Hope this makes sense, Please advise any info, would appreciate it alot. Juan |
Format bases on IF Statement
Hi
try the following - select your range (e.g. A1:D100) - goto 'Format - Conditional Format' - enter the formula =ISNUMBER(FIND("Total",$A1)) - choose your format -- Regards Frank Kabel Frankfurt, Germany JUAN wrote: Hello, I created a subtotal using excel's Subtotal option. Example, DIV NAME2 NAME3 NAME4 QTY Ext TEST1 JX JY JQ 42 52 TEST1 Total 42 52 TEST2 JX JY JQ 54 60 TEST2 Total 54 60 So I want to format Col A,B,C and D only if Row equals TEST1 Total. So something like thits If A:A="TEST Total" Or "TEST2 Total" Then format Col A,B,C D only for that row. Etc.... Hope this makes sense, Please advise any info, would appreciate it alot. Juan |
Format bases on IF Statement
Apply this conditional formatting to cell A1
Formula is =RIGHT($A1,6)=" Total" Font = Red (or whatever format you wanted) Then copy A1 Select all cells paste special: formats -- Rob van Gelder - http://www.vangelder.co.nz/excel "JUAN" wrote in message ... Hello, I created a subtotal using excel's Subtotal option. Example, DIV NAME2 NAME3 NAME4 QTY Ext TEST1 JX JY JQ 42 52 TEST1 Total 42 52 TEST2 JX JY JQ 54 60 TEST2 Total 54 60 So I want to format Col A,B,C and D only if Row equals TEST1 Total. So something like thits If A:A="TEST Total" Or "TEST2 Total" Then format Col A,B,C D only for that row. Etc.... Hope this makes sense, Please advise any info, would appreciate it alot. Juan |
Format bases on IF Statement
Hello,
Frank, thanks this seems to be perfect, but now how would I format those that have empty value, the cell is blank. As i provided example, TEST2 JX JY JQ 54 60 TEST1 Total 42 52 I'm able to format Col A,E AND F using your advise, but I need to format B,C, and D. Those are blank. So not sure if there's a way to format this. I'll continue playing around see if I can figure, but please advise. thank you, juan -----Original Message----- Hi try the following - select your range (e.g. A1:D100) - goto 'Format - Conditional Format' - enter the formula =ISNUMBER(FIND("Total",$A1)) - choose your format -- Regards Frank Kabel Frankfurt, Germany JUAN wrote: Hello, I created a subtotal using excel's Subtotal option. Example, DIV NAME2 NAME3 NAME4 QTY Ext TEST1 JX JY JQ 42 52 TEST1 Total 42 52 TEST2 JX JY JQ 54 60 TEST2 Total 54 60 So I want to format Col A,B,C and D only if Row equals TEST1 Total. So something like thits If A:A="TEST Total" Or "TEST2 Total" Then format Col A,B,C D only for that row. Etc.... Hope this makes sense, Please advise any info, would appreciate it alot. Juan . |
Format bases on IF Statement
Hi
You can format them in the same way (if you choose a specific background color). Just select the entire range (as shown in my previous post) and enter the formula as shown -- Regards Frank Kabel Frankfurt, Germany JUAN wrote: Hello, Frank, thanks this seems to be perfect, but now how would I format those that have empty value, the cell is blank. As i provided example, TEST2 JX JY JQ 54 60 TEST1 Total 42 52 I'm able to format Col A,E AND F using your advise, but I need to format B,C, and D. Those are blank. So not sure if there's a way to format this. I'll continue playing around see if I can figure, but please advise. thank you, juan -----Original Message----- Hi try the following - select your range (e.g. A1:D100) - goto 'Format - Conditional Format' - enter the formula =ISNUMBER(FIND("Total",$A1)) - choose your format -- Regards Frank Kabel Frankfurt, Germany JUAN wrote: Hello, I created a subtotal using excel's Subtotal option. Example, DIV NAME2 NAME3 NAME4 QTY Ext TEST1 JX JY JQ 42 52 TEST1 Total 42 52 TEST2 JX JY JQ 54 60 TEST2 Total 54 60 So I want to format Col A,B,C and D only if Row equals TEST1 Total. So something like thits If A:A="TEST Total" Or "TEST2 Total" Then format Col A,B,C D only for that row. Etc.... Hope this makes sense, Please advise any info, would appreciate it alot. Juan . |
Format bases on IF Statement
Hello Frank,
your previous formula says if ISNUMBER, but those three things are blank. I can't seem to get this to work. please advise. sorry about this, thanks, juan -----Original Message----- Hi You can format them in the same way (if you choose a specific background color). Just select the entire range (as shown in my previous post) and enter the formula as shown -- Regards Frank Kabel Frankfurt, Germany JUAN wrote: Hello, Frank, thanks this seems to be perfect, but now how would I format those that have empty value, the cell is blank. As i provided example, TEST2 JX JY JQ 54 60 TEST1 Total 42 52 I'm able to format Col A,E AND F using your advise, but I need to format B,C, and D. Those are blank. So not sure if there's a way to format this. I'll continue playing around see if I can figure, but please advise. thank you, juan -----Original Message----- Hi try the following - select your range (e.g. A1:D100) - goto 'Format - Conditional Format' - enter the formula =ISNUMBER(FIND("Total",$A1)) - choose your format -- Regards Frank Kabel Frankfurt, Germany JUAN wrote: Hello, I created a subtotal using excel's Subtotal option. Example, DIV NAME2 NAME3 NAME4 QTY Ext TEST1 JX JY JQ 42 52 TEST1 Total 42 52 TEST2 JX JY JQ 54 60 TEST2 Total 54 60 So I want to format Col A,B,C and D only if Row equals TEST1 Total. So something like thits If A:A="TEST Total" Or "TEST2 Total" Then format Col A,B,C D only for that row. Etc.... Hope this makes sense, Please advise any info, would appreciate it alot. Juan . . |
Format bases on IF Statement
Hi
but if I unerstood you you only check column A for the word 'Total'. Therefore I used a mixed aboslute reference like $A1 that is always checking column A. But maybe I'm missing something here. You may repost with an example row of data and state your expected result for all columns :-) -- Regards Frank Kabel Frankfurt, Germany JUAN wrote: Hello Frank, your previous formula says if ISNUMBER, but those three things are blank. I can't seem to get this to work. please advise. sorry about this, thanks, juan -----Original Message----- Hi You can format them in the same way (if you choose a specific background color). Just select the entire range (as shown in my previous post) and enter the formula as shown -- Regards Frank Kabel Frankfurt, Germany JUAN wrote: Hello, Frank, thanks this seems to be perfect, but now how would I format those that have empty value, the cell is blank. As i provided example, TEST2 JX JY JQ 54 60 TEST1 Total 42 52 I'm able to format Col A,E AND F using your advise, but I need to format B,C, and D. Those are blank. So not sure if there's a way to format this. I'll continue playing around see if I can figure, but please advise. thank you, juan -----Original Message----- Hi try the following - select your range (e.g. A1:D100) - goto 'Format - Conditional Format' - enter the formula =ISNUMBER(FIND("Total",$A1)) - choose your format -- Regards Frank Kabel Frankfurt, Germany JUAN wrote: Hello, I created a subtotal using excel's Subtotal option. Example, DIV NAME2 NAME3 NAME4 QTY Ext TEST1 JX JY JQ 42 52 TEST1 Total 42 52 TEST2 JX JY JQ 54 60 TEST2 Total 54 60 So I want to format Col A,B,C and D only if Row equals TEST1 Total. So something like thits If A:A="TEST Total" Or "TEST2 Total" Then format Col A,B,C D only for that row. Etc.... Hope this makes sense, Please advise any info, would appreciate it alot. Juan . . |
| All times are GMT +1. The time now is 02:45 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com