|
Alphabetically list of names BOLD and NOT bold
I have a list of alphabetically last names, first names sorted that I' like to
conditionally format by BOLDED or NOT bolded. All last names startin with letter A = BOLD Going down then B = not bold Then last name starting with letter C could be missing or not: If C last names present then C=BOLD If C does not have any last names then D=BOLD. And so on. I've trying conditionally formatting making up formula is... but nothing works. Column A XL 2003 Windows XP. THANKS. |
Alphabetically list of names BOLD and NOT bold
Hi,
Lest assume that the last names are in A2:A100 with a title in cell A1, then To conditionally format your cell(s): In 2003: 1. Select the cells you want to format (here A2:A100) 2. Choose Format, Conditional Formatting 3. Choose Formula is from the first drop down 4. In the second box enter the formula: =IF(ROW()=2,TRUE,IF(LEFT(A2)=LEFT(A1),C1,NOT(C1))) 5. Click the Format button 6. Choose a color on the Patterns tab (or any available option) 7. Click OK twice. In 2007: 1. Highlight all the cells on the rows you want formatted 2. Choose Home, Conditional Formatting, New Rule 3. Choose Use a formula to determine which cell to format 4. In the Format values where this formula is true enter the following formula: =IF(ROW()=2,TRUE,IF(LEFT(A2)=LEFT(A1),C1,NOT(C1))) 5. Click the Format button and choose a format. 6. Click OK twice -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Lerner" wrote: I have a list of alphabetically last names, first names sorted that I' like to conditionally format by BOLDED or NOT bolded. All last names startin with letter A = BOLD Going down then B = not bold Then last name starting with letter C could be missing or not: If C last names present then C=BOLD If C does not have any last names then D=BOLD. And so on. I've trying conditionally formatting making up formula is... but nothing works. Column A XL 2003 Windows XP. THANKS. |
Alphabetically list of names BOLD and NOT bold
Hi, Shane, thanks for your help, however only the first name gets to be bold
using the given formula. Clarification: There are more than one last name starting with letter A and/or other letters. Also, not only last names starting with letter C could be missing, it could be last names starting with any other letter. ex A2 ABILA, JOHN A3 ANARASCO, JESUS A4 ANTIARO, JANE A5 CORALIO, MIKE A6 CORANIO, NICK In this example last names starting with letter B are missing, like I said it could be any letter as I will be applying the CF to different worksheets with different lists. THANKS. "Shane Devenshire" wrote: Hi, Lest assume that the last names are in A2:A100 with a title in cell A1, then To conditionally format your cell(s): In 2003: 1. Select the cells you want to format (here A2:A100) 2. Choose Format, Conditional Formatting 3. Choose Formula is from the first drop down 4. In the second box enter the formula: =IF(ROW()=2,TRUE,IF(LEFT(A2)=LEFT(A1),C1,NOT(C1))) 5. Click the Format button 6. Choose a color on the Patterns tab (or any available option) 7. Click OK twice. In 2007: 1. Highlight all the cells on the rows you want formatted 2. Choose Home, Conditional Formatting, New Rule 3. Choose Use a formula to determine which cell to format 4. In the Format values where this formula is true enter the following formula: =IF(ROW()=2,TRUE,IF(LEFT(A2)=LEFT(A1),C1,NOT(C1))) 5. Click the Format button and choose a format. 6. Click OK twice -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Lerner" wrote: I have a list of alphabetically last names, first names sorted that I' like to conditionally format by BOLDED or NOT bolded. All last names startin with letter A = BOLD Going down then B = not bold Then last name starting with letter C could be missing or not: If C last names present then C=BOLD If C does not have any last names then D=BOLD. And so on. I've trying conditionally formatting making up formula is... but nothing works. Column A XL 2003 Windows XP. THANKS. |
Alphabetically list of names BOLD and NOT bold
try this
1. Select the cells you want to format (assuming here is A2:A10) 2. Choose Format, Conditional Formatting 3. Choose Formula is from the first drop down 4. In the second box enter the formula: =OR(LEFT(B6)={"A","C","E","G","I","K","M","O"..... ...."Z"}) 5. Click the Format button 6. Click Format and choose a color 7. Click OK twice. Pls note that this formula will not compare the condition on whether there is a C and if not apply format to D. The formula will apply format to alternate alpha from A to Z. Otherwise, you will run out of using the if statement as excel limit up to 7 level of nested IFs -- Hope this is helpful Pls click the Yes button below if this post provide answer you have asked Thank You cheers, francis "Lerner" wrote: Hi, Shane, thanks for your help, however only the first name gets to be bold using the given formula. Clarification: There are more than one last name starting with letter A and/or other letters. Also, not only last names starting with letter C could be missing, it could be last names starting with any other letter. ex A2 ABILA, JOHN A3 ANARASCO, JESUS A4 ANTIARO, JANE A5 CORALIO, MIKE A6 CORANIO, NICK In this example last names starting with letter B are missing, like I said it could be any letter as I will be applying the CF to different worksheets with different lists. THANKS. "Shane Devenshire" wrote: Hi, Lest assume that the last names are in A2:A100 with a title in cell A1, then To conditionally format your cell(s): In 2003: 1. Select the cells you want to format (here A2:A100) 2. Choose Format, Conditional Formatting 3. Choose Formula is from the first drop down 4. In the second box enter the formula: =IF(ROW()=2,TRUE,IF(LEFT(A2)=LEFT(A1),C1,NOT(C1))) 5. Click the Format button 6. Choose a color on the Patterns tab (or any available option) 7. Click OK twice. In 2007: 1. Highlight all the cells on the rows you want formatted 2. Choose Home, Conditional Formatting, New Rule 3. Choose Use a formula to determine which cell to format 4. In the Format values where this formula is true enter the following formula: =IF(ROW()=2,TRUE,IF(LEFT(A2)=LEFT(A1),C1,NOT(C1))) 5. Click the Format button and choose a format. 6. Click OK twice -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Lerner" wrote: I have a list of alphabetically last names, first names sorted that I' like to conditionally format by BOLDED or NOT bolded. All last names startin with letter A = BOLD Going down then B = not bold Then last name starting with letter C could be missing or not: If C last names present then C=BOLD If C does not have any last names then D=BOLD. And so on. I've trying conditionally formatting making up formula is... but nothing works. Column A XL 2003 Windows XP. THANKS. |
Alphabetically list of names BOLD and NOT bold
Following macro will BOLD the first letter on Last Names in Col A in
alternate way as desired by you... Use this macro; ' See http://www.rondebruin.nl/code.htm if you need help on how to run macros Sub highlight() Dim lastRow, lastCol As Double Dim pName As String Dim flg As Boolean With ActiveSheet lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row lastCol = .Cells(.Columns.Count, "A").End(xlToLeft).Column End With pName = "" flg = True For i = 2 To lastRow If pName < Left(Cells(i, 1).Value, 1) Then pName = Left(Cells(i, 1).Value, 1) If flg = True Then flg = False Else flg = True End If End If If flg = True Then 'format With Cells(i, 1).Characters(Start:=1, Length:=1).Font .FontStyle = "Bold" End With With Cells(i, 1).Characters(Start:=2, Length:=255).Font .FontStyle = "Regualar" End With End If Next End Sub "Lerner" wrote: Hi, Shane, thanks for your help, however only the first name gets to be bold using the given formula. Clarification: There are more than one last name starting with letter A and/or other letters. Also, not only last names starting with letter C could be missing, it could be last names starting with any other letter. ex A2 ABILA, JOHN A3 ANARASCO, JESUS A4 ANTIARO, JANE A5 CORALIO, MIKE A6 CORANIO, NICK In this example last names starting with letter B are missing, like I said it could be any letter as I will be applying the CF to different worksheets with different lists. THANKS. "Shane Devenshire" wrote: Hi, Lest assume that the last names are in A2:A100 with a title in cell A1, then To conditionally format your cell(s): In 2003: 1. Select the cells you want to format (here A2:A100) 2. Choose Format, Conditional Formatting 3. Choose Formula is from the first drop down 4. In the second box enter the formula: =IF(ROW()=2,TRUE,IF(LEFT(A2)=LEFT(A1),C1,NOT(C1))) 5. Click the Format button 6. Choose a color on the Patterns tab (or any available option) 7. Click OK twice. In 2007: 1. Highlight all the cells on the rows you want formatted 2. Choose Home, Conditional Formatting, New Rule 3. Choose Use a formula to determine which cell to format 4. In the Format values where this formula is true enter the following formula: =IF(ROW()=2,TRUE,IF(LEFT(A2)=LEFT(A1),C1,NOT(C1))) 5. Click the Format button and choose a format. 6. Click OK twice -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Lerner" wrote: I have a list of alphabetically last names, first names sorted that I' like to conditionally format by BOLDED or NOT bolded. All last names startin with letter A = BOLD Going down then B = not bold Then last name starting with letter C could be missing or not: If C last names present then C=BOLD If C does not have any last names then D=BOLD. And so on. I've trying conditionally formatting making up formula is... but nothing works. Column A XL 2003 Windows XP. THANKS. |
Alphabetically list of names BOLD and NOT bold
Hi,
Sorry, lets try this instead - create a dummy column, say for this example C. In C2 enter the following formula: =IF(ROW()=2,TRUE,IF(LEFT(B2)=LEFT(B1),C1,NOT(C1))) Hide this column if desired. Now highlight A2:B100 or wherever your first and last names are and proceed as in the earlier discussion on conditional formatting but change the formula to read: =$C2 You can avoid the dummy column by writing a VBA function but its not worth the effort in my opinion. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Lerner" wrote: Hi, Shane, thanks for your help, however only the first name gets to be bold using the given formula. Clarification: There are more than one last name starting with letter A and/or other letters. Also, not only last names starting with letter C could be missing, it could be last names starting with any other letter. ex A2 ABILA, JOHN A3 ANARASCO, JESUS A4 ANTIARO, JANE A5 CORALIO, MIKE A6 CORANIO, NICK In this example last names starting with letter B are missing, like I said it could be any letter as I will be applying the CF to different worksheets with different lists. THANKS. "Shane Devenshire" wrote: Hi, Lest assume that the last names are in A2:A100 with a title in cell A1, then To conditionally format your cell(s): In 2003: 1. Select the cells you want to format (here A2:A100) 2. Choose Format, Conditional Formatting 3. Choose Formula is from the first drop down 4. In the second box enter the formula: =IF(ROW()=2,TRUE,IF(LEFT(A2)=LEFT(A1),C1,NOT(C1))) 5. Click the Format button 6. Choose a color on the Patterns tab (or any available option) 7. Click OK twice. In 2007: 1. Highlight all the cells on the rows you want formatted 2. Choose Home, Conditional Formatting, New Rule 3. Choose Use a formula to determine which cell to format 4. In the Format values where this formula is true enter the following formula: =IF(ROW()=2,TRUE,IF(LEFT(A2)=LEFT(A1),C1,NOT(C1))) 5. Click the Format button and choose a format. 6. Click OK twice -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Lerner" wrote: I have a list of alphabetically last names, first names sorted that I' like to conditionally format by BOLDED or NOT bolded. All last names startin with letter A = BOLD Going down then B = not bold Then last name starting with letter C could be missing or not: If C last names present then C=BOLD If C does not have any last names then D=BOLD. And so on. I've trying conditionally formatting making up formula is... but nothing works. Column A XL 2003 Windows XP. THANKS. |
Alphabetically list of names BOLD and NOT bold
I could live with " bold and not bold A-Z" eventough is not specifically
my goal as explained. However with your formula I get this: "You may not use unions, intersections, for conditional formating criteria". Thanks. "francis" wrote: try this 1. Select the cells you want to format (assuming here is A2:A10) 2. Choose Format, Conditional Formatting 3. Choose Formula is from the first drop down 4. In the second box enter the formula: =OR(LEFT(B6)={"A","C","E","G","I","K","M","O"..... ...."Z"}) 5. Click the Format button 6. Click Format and choose a color 7. Click OK twice. Pls note that this formula will not compare the condition on whether there is a C and if not apply format to D. The formula will apply format to alternate alpha from A to Z. Otherwise, you will run out of using the if statement as excel limit up to 7 level of nested IFs -- Hope this is helpful Pls click the Yes button below if this post provide answer you have asked Thank You cheers, francis "Lerner" wrote: Hi, Shane, thanks for your help, however only the first name gets to be bold using the given formula. Clarification: There are more than one last name starting with letter A and/or other letters. Also, not only last names starting with letter C could be missing, it could be last names starting with any other letter. ex A2 ABILA, JOHN A3 ANARASCO, JESUS A4 ANTIARO, JANE A5 CORALIO, MIKE A6 CORANIO, NICK In this example last names starting with letter B are missing, like I said it could be any letter as I will be applying the CF to different worksheets with different lists. THANKS. "Shane Devenshire" wrote: Hi, Lest assume that the last names are in A2:A100 with a title in cell A1, then To conditionally format your cell(s): In 2003: 1. Select the cells you want to format (here A2:A100) 2. Choose Format, Conditional Formatting 3. Choose Formula is from the first drop down 4. In the second box enter the formula: =IF(ROW()=2,TRUE,IF(LEFT(A2)=LEFT(A1),C1,NOT(C1))) 5. Click the Format button 6. Choose a color on the Patterns tab (or any available option) 7. Click OK twice. In 2007: 1. Highlight all the cells on the rows you want formatted 2. Choose Home, Conditional Formatting, New Rule 3. Choose Use a formula to determine which cell to format 4. In the Format values where this formula is true enter the following formula: =IF(ROW()=2,TRUE,IF(LEFT(A2)=LEFT(A1),C1,NOT(C1))) 5. Click the Format button and choose a format. 6. Click OK twice -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Lerner" wrote: I have a list of alphabetically last names, first names sorted that I' like to conditionally format by BOLDED or NOT bolded. All last names startin with letter A = BOLD Going down then B = not bold Then last name starting with letter C could be missing or not: If C last names present then C=BOLD If C does not have any last names then D=BOLD. And so on. I've trying conditionally formatting making up formula is... but nothing works. Column A XL 2003 Windows XP. THANKS. |
Alphabetically list of names BOLD and NOT bold
Eventough I don't like MACROS, if nothing else will help then:
Where or how I do the pasting of this macro. I tried right clicking in the SHEET TAB going to VIEW and pasted it there but did not do anything. Thanks. "Sheeloo" wrote: Following macro will BOLD the first letter on Last Names in Col A in alternate way as desired by you... Use this macro; ' See http://www.rondebruin.nl/code.htm if you need help on how to run macros Sub highlight() Dim lastRow, lastCol As Double Dim pName As String Dim flg As Boolean With ActiveSheet lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row lastCol = .Cells(.Columns.Count, "A").End(xlToLeft).Column End With pName = "" flg = True For i = 2 To lastRow If pName < Left(Cells(i, 1).Value, 1) Then pName = Left(Cells(i, 1).Value, 1) If flg = True Then flg = False Else flg = True End If End If If flg = True Then 'format With Cells(i, 1).Characters(Start:=1, Length:=1).Font .FontStyle = "Bold" End With With Cells(i, 1).Characters(Start:=2, Length:=255).Font .FontStyle = "Regualar" End With End If Next End Sub "Lerner" wrote: Hi, Shane, thanks for your help, however only the first name gets to be bold using the given formula. Clarification: There are more than one last name starting with letter A and/or other letters. Also, not only last names starting with letter C could be missing, it could be last names starting with any other letter. ex A2 ABILA, JOHN A3 ANARASCO, JESUS A4 ANTIARO, JANE A5 CORALIO, MIKE A6 CORANIO, NICK In this example last names starting with letter B are missing, like I said it could be any letter as I will be applying the CF to different worksheets with different lists. THANKS. "Shane Devenshire" wrote: Hi, Lest assume that the last names are in A2:A100 with a title in cell A1, then To conditionally format your cell(s): In 2003: 1. Select the cells you want to format (here A2:A100) 2. Choose Format, Conditional Formatting 3. Choose Formula is from the first drop down 4. In the second box enter the formula: =IF(ROW()=2,TRUE,IF(LEFT(A2)=LEFT(A1),C1,NOT(C1))) 5. Click the Format button 6. Choose a color on the Patterns tab (or any available option) 7. Click OK twice. In 2007: 1. Highlight all the cells on the rows you want formatted 2. Choose Home, Conditional Formatting, New Rule 3. Choose Use a formula to determine which cell to format 4. In the Format values where this formula is true enter the following formula: =IF(ROW()=2,TRUE,IF(LEFT(A2)=LEFT(A1),C1,NOT(C1))) 5. Click the Format button and choose a format. 6. Click OK twice -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Lerner" wrote: I have a list of alphabetically last names, first names sorted that I' like to conditionally format by BOLDED or NOT bolded. All last names startin with letter A = BOLD Going down then B = not bold Then last name starting with letter C could be missing or not: If C last names present then C=BOLD If C does not have any last names then D=BOLD. And so on. I've trying conditionally formatting making up formula is... but nothing works. Column A XL 2003 Windows XP. THANKS. |
Alphabetically list of names BOLD and NOT bold
Thanks again Shane, but I got just the first Last name BOLD.
"Shane Devenshire" wrote: Hi, Sorry, lets try this instead - create a dummy column, say for this example C. In C2 enter the following formula: =IF(ROW()=2,TRUE,IF(LEFT(B2)=LEFT(B1),C1,NOT(C1))) Hide this column if desired. Now highlight A2:B100 or wherever your first and last names are and proceed as in the earlier discussion on conditional formatting but change the formula to read: =$C2 You can avoid the dummy column by writing a VBA function but its not worth the effort in my opinion. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Lerner" wrote: Hi, Shane, thanks for your help, however only the first name gets to be bold using the given formula. Clarification: There are more than one last name starting with letter A and/or other letters. Also, not only last names starting with letter C could be missing, it could be last names starting with any other letter. ex A2 ABILA, JOHN A3 ANARASCO, JESUS A4 ANTIARO, JANE A5 CORALIO, MIKE A6 CORANIO, NICK In this example last names starting with letter B are missing, like I said it could be any letter as I will be applying the CF to different worksheets with different lists. THANKS. "Shane Devenshire" wrote: Hi, Lest assume that the last names are in A2:A100 with a title in cell A1, then To conditionally format your cell(s): In 2003: 1. Select the cells you want to format (here A2:A100) 2. Choose Format, Conditional Formatting 3. Choose Formula is from the first drop down 4. In the second box enter the formula: =IF(ROW()=2,TRUE,IF(LEFT(A2)=LEFT(A1),C1,NOT(C1))) 5. Click the Format button 6. Choose a color on the Patterns tab (or any available option) 7. Click OK twice. In 2007: 1. Highlight all the cells on the rows you want formatted 2. Choose Home, Conditional Formatting, New Rule 3. Choose Use a formula to determine which cell to format 4. In the Format values where this formula is true enter the following formula: =IF(ROW()=2,TRUE,IF(LEFT(A2)=LEFT(A1),C1,NOT(C1))) 5. Click the Format button and choose a format. 6. Click OK twice -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Lerner" wrote: I have a list of alphabetically last names, first names sorted that I' like to conditionally format by BOLDED or NOT bolded. All last names startin with letter A = BOLD Going down then B = not bold Then last name starting with letter C could be missing or not: If C last names present then C=BOLD If C does not have any last names then D=BOLD. And so on. I've trying conditionally formatting making up formula is... but nothing works. Column A XL 2003 Windows XP. THANKS. |
Alphabetically list of names BOLD and NOT bold
Well, one of the ways to avoid macros is not to have requirements which need
macros :-) To run this macro Open the worksheet you want it to run on Press ALT-F11 which will bring up the VB Editor Double click on the name of the your workbook in the left pane Click Insert- Module Paste the code in the right pane Press F5 to run the code Switch to your worksheet.. Next time you can choose Tool-Macros.... Run "Lerner" wrote: Eventough I don't like MACROS, if nothing else will help then: Where or how I do the pasting of this macro. I tried right clicking in the SHEET TAB going to VIEW and pasted it there but did not do anything. Thanks. "Sheeloo" wrote: Following macro will BOLD the first letter on Last Names in Col A in alternate way as desired by you... Use this macro; ' See http://www.rondebruin.nl/code.htm if you need help on how to run macros Sub highlight() Dim lastRow, lastCol As Double Dim pName As String Dim flg As Boolean With ActiveSheet lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row lastCol = .Cells(.Columns.Count, "A").End(xlToLeft).Column End With pName = "" flg = True For i = 2 To lastRow If pName < Left(Cells(i, 1).Value, 1) Then pName = Left(Cells(i, 1).Value, 1) If flg = True Then flg = False Else flg = True End If End If If flg = True Then 'format With Cells(i, 1).Characters(Start:=1, Length:=1).Font .FontStyle = "Bold" End With With Cells(i, 1).Characters(Start:=2, Length:=255).Font .FontStyle = "Regualar" End With End If Next End Sub "Lerner" wrote: Hi, Shane, thanks for your help, however only the first name gets to be bold using the given formula. Clarification: There are more than one last name starting with letter A and/or other letters. Also, not only last names starting with letter C could be missing, it could be last names starting with any other letter. ex A2 ABILA, JOHN A3 ANARASCO, JESUS A4 ANTIARO, JANE A5 CORALIO, MIKE A6 CORANIO, NICK In this example last names starting with letter B are missing, like I said it could be any letter as I will be applying the CF to different worksheets with different lists. THANKS. "Shane Devenshire" wrote: Hi, Lest assume that the last names are in A2:A100 with a title in cell A1, then To conditionally format your cell(s): In 2003: 1. Select the cells you want to format (here A2:A100) 2. Choose Format, Conditional Formatting 3. Choose Formula is from the first drop down 4. In the second box enter the formula: =IF(ROW()=2,TRUE,IF(LEFT(A2)=LEFT(A1),C1,NOT(C1))) 5. Click the Format button 6. Choose a color on the Patterns tab (or any available option) 7. Click OK twice. In 2007: 1. Highlight all the cells on the rows you want formatted 2. Choose Home, Conditional Formatting, New Rule 3. Choose Use a formula to determine which cell to format 4. In the Format values where this formula is true enter the following formula: =IF(ROW()=2,TRUE,IF(LEFT(A2)=LEFT(A1),C1,NOT(C1))) 5. Click the Format button and choose a format. 6. Click OK twice -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Lerner" wrote: I have a list of alphabetically last names, first names sorted that I' like to conditionally format by BOLDED or NOT bolded. All last names startin with letter A = BOLD Going down then B = not bold Then last name starting with letter C could be missing or not: If C last names present then C=BOLD If C does not have any last names then D=BOLD. And so on. I've trying conditionally formatting making up formula is... but nothing works. Column A XL 2003 Windows XP. THANKS. |
Alphabetically list of names BOLD and NOT bold
Try with a helper column. say in Z1, which you can hide it,
place the formula provided. place this formula in the Conditional Formatting =Z1=TRUE -- Hope this is helpful Pls click the Yes button below if this post provide answer you have asked Thank You cheers, francis "Lerner" wrote: I could live with " bold and not bold A-Z" eventough is not specifically my goal as explained. However with your formula I get this: "You may not use unions, intersections, for conditional formating criteria". Thanks. "francis" wrote: try this 1. Select the cells you want to format (assuming here is A2:A10) 2. Choose Format, Conditional Formatting 3. Choose Formula is from the first drop down 4. In the second box enter the formula: =OR(LEFT(B6)={"A","C","E","G","I","K","M","O"..... ...."Z"}) 5. Click the Format button 6. Click Format and choose a color 7. Click OK twice. Pls note that this formula will not compare the condition on whether there is a C and if not apply format to D. The formula will apply format to alternate alpha from A to Z. Otherwise, you will run out of using the if statement as excel limit up to 7 level of nested IFs -- Hope this is helpful Pls click the Yes button below if this post provide answer you have asked Thank You cheers, francis "Lerner" wrote: Hi, Shane, thanks for your help, however only the first name gets to be bold using the given formula. Clarification: There are more than one last name starting with letter A and/or other letters. Also, not only last names starting with letter C could be missing, it could be last names starting with any other letter. ex A2 ABILA, JOHN A3 ANARASCO, JESUS A4 ANTIARO, JANE A5 CORALIO, MIKE A6 CORANIO, NICK In this example last names starting with letter B are missing, like I said it could be any letter as I will be applying the CF to different worksheets with different lists. THANKS. "Shane Devenshire" wrote: Hi, Lest assume that the last names are in A2:A100 with a title in cell A1, then To conditionally format your cell(s): In 2003: 1. Select the cells you want to format (here A2:A100) 2. Choose Format, Conditional Formatting 3. Choose Formula is from the first drop down 4. In the second box enter the formula: =IF(ROW()=2,TRUE,IF(LEFT(A2)=LEFT(A1),C1,NOT(C1))) 5. Click the Format button 6. Choose a color on the Patterns tab (or any available option) 7. Click OK twice. In 2007: 1. Highlight all the cells on the rows you want formatted 2. Choose Home, Conditional Formatting, New Rule 3. Choose Use a formula to determine which cell to format 4. In the Format values where this formula is true enter the following formula: =IF(ROW()=2,TRUE,IF(LEFT(A2)=LEFT(A1),C1,NOT(C1))) 5. Click the Format button and choose a format. 6. Click OK twice -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Lerner" wrote: I have a list of alphabetically last names, first names sorted that I' like to conditionally format by BOLDED or NOT bolded. All last names startin with letter A = BOLD Going down then B = not bold Then last name starting with letter C could be missing or not: If C last names present then C=BOLD If C does not have any last names then D=BOLD. And so on. I've trying conditionally formatting making up formula is... but nothing works. Column A XL 2003 Windows XP. THANKS. |
Alphabetically list of names BOLD and NOT bold
Lerner
Does any of the solutions works for you? Pls share the outcome if possible. if not, I will provide you another solution. -- Hope this is helpful Pls click the Yes button below if this post provide answer you have asked Thank You cheers, francis "Lerner" wrote: Thanks again Shane, but I got just the first Last name BOLD. "Shane Devenshire" wrote: Hi, Sorry, lets try this instead - create a dummy column, say for this example C. In C2 enter the following formula: =IF(ROW()=2,TRUE,IF(LEFT(B2)=LEFT(B1),C1,NOT(C1))) Hide this column if desired. Now highlight A2:B100 or wherever your first and last names are and proceed as in the earlier discussion on conditional formatting but change the formula to read: =$C2 You can avoid the dummy column by writing a VBA function but its not worth the effort in my opinion. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Lerner" wrote: Hi, Shane, thanks for your help, however only the first name gets to be bold using the given formula. Clarification: There are more than one last name starting with letter A and/or other letters. Also, not only last names starting with letter C could be missing, it could be last names starting with any other letter. ex A2 ABILA, JOHN A3 ANARASCO, JESUS A4 ANTIARO, JANE A5 CORALIO, MIKE A6 CORANIO, NICK In this example last names starting with letter B are missing, like I said it could be any letter as I will be applying the CF to different worksheets with different lists. THANKS. "Shane Devenshire" wrote: Hi, Lest assume that the last names are in A2:A100 with a title in cell A1, then To conditionally format your cell(s): In 2003: 1. Select the cells you want to format (here A2:A100) 2. Choose Format, Conditional Formatting 3. Choose Formula is from the first drop down 4. In the second box enter the formula: =IF(ROW()=2,TRUE,IF(LEFT(A2)=LEFT(A1),C1,NOT(C1))) 5. Click the Format button 6. Choose a color on the Patterns tab (or any available option) 7. Click OK twice. In 2007: 1. Highlight all the cells on the rows you want formatted 2. Choose Home, Conditional Formatting, New Rule 3. Choose Use a formula to determine which cell to format 4. In the Format values where this formula is true enter the following formula: =IF(ROW()=2,TRUE,IF(LEFT(A2)=LEFT(A1),C1,NOT(C1))) 5. Click the Format button and choose a format. 6. Click OK twice -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Lerner" wrote: I have a list of alphabetically last names, first names sorted that I' like to conditionally format by BOLDED or NOT bolded. All last names startin with letter A = BOLD Going down then B = not bold Then last name starting with letter C could be missing or not: If C last names present then C=BOLD If C does not have any last names then D=BOLD. And so on. I've trying conditionally formatting making up formula is... but nothing works. Column A XL 2003 Windows XP. THANKS. |
Alphabetically list of names BOLD and NOT bold
Actually none of them had work.
A B C 1 NAMES 2 ARIES, JOHN 3 ACTUAL, JANE 4 CONSECUTIVE, MIKE 5 EXCEL, MICROSOFT 6 EVEREST, TRISIA 7 HINGS, PAULA Basically the thing is get the first one ( last name with A) BOLD and then alternating with the list (not bold, bold, not bold, bold) regardless of the missing secuency as in this case it will be ABOLD, Cnot bold, E bold, H not bold. I prefer conditional formatting, also I'm posting this again in a new post. Thanks. "francis" wrote: Lerner Does any of the solutions works for you? Pls share the outcome if possible. if not, I will provide you another solution. -- Hope this is helpful Pls click the Yes button below if this post provide answer you have asked Thank You cheers, francis "Lerner" wrote: Thanks again Shane, but I got just the first Last name BOLD. "Shane Devenshire" wrote: Hi, Sorry, lets try this instead - create a dummy column, say for this example C. In C2 enter the following formula: =IF(ROW()=2,TRUE,IF(LEFT(B2)=LEFT(B1),C1,NOT(C1))) Hide this column if desired. Now highlight A2:B100 or wherever your first and last names are and proceed as in the earlier discussion on conditional formatting but change the formula to read: =$C2 You can avoid the dummy column by writing a VBA function but its not worth the effort in my opinion. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Lerner" wrote: Hi, Shane, thanks for your help, however only the first name gets to be bold using the given formula. Clarification: There are more than one last name starting with letter A and/or other letters. Also, not only last names starting with letter C could be missing, it could be last names starting with any other letter. ex A2 ABILA, JOHN A3 ANARASCO, JESUS A4 ANTIARO, JANE A5 CORALIO, MIKE A6 CORANIO, NICK In this example last names starting with letter B are missing, like I said it could be any letter as I will be applying the CF to different worksheets with different lists. THANKS. "Shane Devenshire" wrote: Hi, Lest assume that the last names are in A2:A100 with a title in cell A1, then To conditionally format your cell(s): In 2003: 1. Select the cells you want to format (here A2:A100) 2. Choose Format, Conditional Formatting 3. Choose Formula is from the first drop down 4. In the second box enter the formula: =IF(ROW()=2,TRUE,IF(LEFT(A2)=LEFT(A1),C1,NOT(C1))) 5. Click the Format button 6. Choose a color on the Patterns tab (or any available option) 7. Click OK twice. In 2007: 1. Highlight all the cells on the rows you want formatted 2. Choose Home, Conditional Formatting, New Rule 3. Choose Use a formula to determine which cell to format 4. In the Format values where this formula is true enter the following formula: =IF(ROW()=2,TRUE,IF(LEFT(A2)=LEFT(A1),C1,NOT(C1))) 5. Click the Format button and choose a format. 6. Click OK twice -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Lerner" wrote: I have a list of alphabetically last names, first names sorted that I' like to conditionally format by BOLDED or NOT bolded. All last names startin with letter A = BOLD Going down then B = not bold Then last name starting with letter C could be missing or not: If C last names present then C=BOLD If C does not have any last names then D=BOLD. And so on. I've trying conditionally formatting making up formula is... but nothing works. Column A XL 2003 Windows XP. THANKS. |
Alphabetically list of names BOLD and NOT bold
Lerner
It is always better to stick to this thread rather than starting a new post and provide your feedback on the solutions provided work or not. Create a helper column, let say col B, try this formula in B2 and copy as far down as your data is. This is use to control your conditional formatting in Col A. You can place this helper column out of the way and hide it. =IF(LEFT(A2)=LEFT(A1),MOD(B1,2),MOD(B1+1,2)) Select those cells in col A that you want to apply CF place this formula for Conditional Formatting =B1=0 This assume that you know how to apply formatting in CF, if not see the earlier posts which described the process. This should work as I have tested it for a fair amount of time last nite Let me know if this is what you want. Remember to click on the Yes button as your feedback and this will also help others to search the archive better in future. -- Hope this is helpful Pls click the Yes button below if this post provide answer you have asked Thank You cheers, francis "Lerner" wrote: Actually none of them had work. A B C 1 NAMES 2 ARIES, JOHN 3 ACTUAL, JANE 4 CONSECUTIVE, MIKE 5 EXCEL, MICROSOFT 6 EVEREST, TRISIA 7 HINGS, PAULA Basically the thing is get the first one ( last name with A) BOLD and then alternating with the list (not bold, bold, not bold, bold) regardless of the missing secuency as in this case it will be ABOLD, Cnot bold, E bold, H not bold. I prefer conditional formatting, also I'm posting this again in a new post. Thanks. "francis" wrote: Lerner Does any of the solutions works for you? Pls share the outcome if possible. if not, I will provide you another solution. -- Hope this is helpful Pls click the Yes button below if this post provide answer you have asked Thank You cheers, francis "Lerner" wrote: Thanks again Shane, but I got just the first Last name BOLD. "Shane Devenshire" wrote: Hi, Sorry, lets try this instead - create a dummy column, say for this example C. In C2 enter the following formula: =IF(ROW()=2,TRUE,IF(LEFT(B2)=LEFT(B1),C1,NOT(C1))) Hide this column if desired. Now highlight A2:B100 or wherever your first and last names are and proceed as in the earlier discussion on conditional formatting but change the formula to read: =$C2 You can avoid the dummy column by writing a VBA function but its not worth the effort in my opinion. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Lerner" wrote: Hi, Shane, thanks for your help, however only the first name gets to be bold using the given formula. Clarification: There are more than one last name starting with letter A and/or other letters. Also, not only last names starting with letter C could be missing, it could be last names starting with any other letter. ex A2 ABILA, JOHN A3 ANARASCO, JESUS A4 ANTIARO, JANE A5 CORALIO, MIKE A6 CORANIO, NICK In this example last names starting with letter B are missing, like I said it could be any letter as I will be applying the CF to different worksheets with different lists. THANKS. "Shane Devenshire" wrote: Hi, Lest assume that the last names are in A2:A100 with a title in cell A1, then To conditionally format your cell(s): In 2003: 1. Select the cells you want to format (here A2:A100) 2. Choose Format, Conditional Formatting 3. Choose Formula is from the first drop down 4. In the second box enter the formula: =IF(ROW()=2,TRUE,IF(LEFT(A2)=LEFT(A1),C1,NOT(C1))) 5. Click the Format button 6. Choose a color on the Patterns tab (or any available option) 7. Click OK twice. In 2007: 1. Highlight all the cells on the rows you want formatted 2. Choose Home, Conditional Formatting, New Rule 3. Choose Use a formula to determine which cell to format 4. In the Format values where this formula is true enter the following formula: =IF(ROW()=2,TRUE,IF(LEFT(A2)=LEFT(A1),C1,NOT(C1))) 5. Click the Format button and choose a format. 6. Click OK twice -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Lerner" wrote: I have a list of alphabetically last names, first names sorted that I' like to conditionally format by BOLDED or NOT bolded. All last names startin with letter A = BOLD Going down then B = not bold Then last name starting with letter C could be missing or not: If C last names present then C=BOLD If C does not have any last names then D=BOLD. And so on. I've trying conditionally formatting making up formula is... but nothing works. Column A XL 2003 Windows XP. THANKS. |
| All times are GMT +1. The time now is 08:10 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com