| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 . |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 . |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 . . |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 . . |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Chart bases on user input dates | Charts and Charting in Excel | |||
| formula for commissions on a per piece bases | Excel Worksheet Functions | |||
| Result required in 365-Days Bases | Excel Worksheet Functions | |||
| pmt() uses 360-Days Bases, I need 365-Days Bases | Excel Worksheet Functions | |||
| pmt() uses 360-Days Bases, I need 365-Days Bases | Excel Worksheet Functions | |||
Hybrid Mode
