|
Exce Formating
Hi: I am trying to format an excel worksheet based on acount number changing
I have 3000 records and want the row to change with the account number changes. any ideas? Example: Account Number Date: Amount: 1234 12/31/2009 1.20 1234 12/31/2009 2.29 1234 12/31/2009 3.40 highlight here 5678 12/31/2009 4.59 5678 12/31/2009 4.59 hightlight here -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200905/1 |
Exce Formating
Assuming you have continuous sorted account numbers in ColA with headers in
Row1 ; run the below macro to insert a blank row after each section and highlight colA to ColC of the blank row inserted. Please try and feedback If you are new to macros launch VBE using Alt+F11. Insert module and paste the below code. Save. Close VBE. Get back to workbook. From tools macro run the below macro. In 2007 uner View menu |View Macros Sub HighlightSections() Dim lngRow As Long Dim lngLastRow As Long Dim varAccNumber As Variant lngLastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row varAccNumber = Range("A" & lngLastRow) For lngRow = lngLastRow To 2 Step -1 If varAccNumber < Range("A" & lngRow) Then varAccNumber = Range("A" & lngRow) lngRow = lngRow + 1 Rows(lngRow).Insert Range("A" & lngRow & ":C" & lngRow).Interior.Color = vbYellow End If Next End Sub If this post helps click Yes --------------- Jacob Skaria "Laura1 via OfficeKB.com" wrote: Hi: I am trying to format an excel worksheet based on acount number changing I have 3000 records and want the row to change with the account number changes. any ideas? Example: Account Number Date: Amount: 1234 12/31/2009 1.20 1234 12/31/2009 2.29 1234 12/31/2009 3.40 highlight here 5678 12/31/2009 4.59 5678 12/31/2009 4.59 hightlight here -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200905/1 |
Exce Formating
Jacob has given you an answer and it may be what you were after, but I found
your question to be unclear. What did you mean be "highlight here"... you show several blanks rows after it and is not clear to me if that is what you want to insert or if you had something else in mind for your "highlight". Can you clarify how you want your data to look afterwards? -- Rick (MVP - Excel) "Laura1 via OfficeKB.com" <u31091@uwe wrote in message news:957aaf4cc0628@uwe... Hi: I am trying to format an excel worksheet based on acount number changing I have 3000 records and want the row to change with the account number changes. any ideas? Example: Account Number Date: Amount: 1234 12/31/2009 1.20 1234 12/31/2009 2.29 1234 12/31/2009 3.40 highlight here 5678 12/31/2009 4.59 5678 12/31/2009 4.59 hightlight here -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200905/1 |
Exce Formating
Yes this is exactly what I wanted thank you!!!!! The only thing if possible
can I get the hightlight to be the length of the entire row with data? NOT a big deal I can live with the other too!!! Thank you! So very much! Jacob Skaria wrote: Assuming you have continuous sorted account numbers in ColA with headers in Row1 ; run the below macro to insert a blank row after each section and highlight colA to ColC of the blank row inserted. Please try and feedback If you are new to macros launch VBE using Alt+F11. Insert module and paste the below code. Save. Close VBE. Get back to workbook. From tools macro run the below macro. In 2007 uner View menu |View Macros Sub HighlightSections() Dim lngRow As Long Dim lngLastRow As Long Dim varAccNumber As Variant lngLastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row varAccNumber = Range("A" & lngLastRow) For lngRow = lngLastRow To 2 Step -1 If varAccNumber < Range("A" & lngRow) Then varAccNumber = Range("A" & lngRow) lngRow = lngRow + 1 Rows(lngRow).Insert Range("A" & lngRow & ":C" & lngRow).Interior.Color = vbYellow End If Next End Sub If this post helps click Yes --------------- Jacob Skaria Hi: I am trying to format an excel worksheet based on acount number changing [quoted text clipped - 12 lines] 5678 12/31/2009 4.59 hightlight here -- Message posted via http://www.officekb.com |
Exce Formating
Yes this is exactly what I wanted thank you!!!!! The only thing if possible
can I get the hightlight to be the length of the entire row with data? NOT a big deal I can live with the other too!!! Thank you! So very much! Jacob Skaria wrote: Assuming you have continuous sorted account numbers in ColA with headers in Row1 ; run the below macro to insert a blank row after each section and highlight colA to ColC of the blank row inserted. Please try and feedback If you are new to macros launch VBE using Alt+F11. Insert module and paste the below code. Save. Close VBE. Get back to workbook. From tools macro run the below macro. In 2007 uner View menu |View Macros Sub HighlightSections() Dim lngRow As Long Dim lngLastRow As Long Dim varAccNumber As Variant lngLastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row varAccNumber = Range("A" & lngLastRow) For lngRow = lngLastRow To 2 Step -1 If varAccNumber < Range("A" & lngRow) Then varAccNumber = Range("A" & lngRow) lngRow = lngRow + 1 Rows(lngRow).Insert Range("A" & lngRow & ":C" & lngRow).Interior.Color = vbYellow End If Next End Sub If this post helps click Yes --------------- Jacob Skaria Hi: I am trying to format an excel worksheet based on acount number changing [quoted text clipped - 12 lines] 5678 12/31/2009 4.59 hightlight here -- Message posted via http://www.officekb.com |
Exce Formating
See if this modification to Jacob's code does what you want...
Sub HighlightSections() Dim lngRow As Long Dim lngLastRow As Long Dim lngLastCol As Long Dim varAccNumber As Variant lngLastRow = Cells(Rows.Count, "A").End(xlUp).Row lngLastCol = Cells.Find(What:="*", SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious).Column varAccNumber = Range("A" & lngLastRow) For lngRow = lngLastRow To 2 Step -1 If varAccNumber < Range("A" & lngRow) Then varAccNumber = Range("A" & lngRow) lngRow = lngRow + 1 Rows(lngRow).Insert Range(Range("A" & lngRow), Cells(lngRow, lngLastCol)). _ Interior.Color = vbYellow End If Next End Sub -- Rick (MVP - Excel) "Laura1 via OfficeKB.com" <u31091@uwe wrote in message news:957ba31de2726@uwe... Yes this is exactly what I wanted thank you!!!!! The only thing if possible can I get the hightlight to be the length of the entire row with data? NOT a big deal I can live with the other too!!! Thank you! So very much! Jacob Skaria wrote: Assuming you have continuous sorted account numbers in ColA with headers in Row1 ; run the below macro to insert a blank row after each section and highlight colA to ColC of the blank row inserted. Please try and feedback If you are new to macros launch VBE using Alt+F11. Insert module and paste the below code. Save. Close VBE. Get back to workbook. From tools macro run the below macro. In 2007 uner View menu |View Macros Sub HighlightSections() Dim lngRow As Long Dim lngLastRow As Long Dim varAccNumber As Variant lngLastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row varAccNumber = Range("A" & lngLastRow) For lngRow = lngLastRow To 2 Step -1 If varAccNumber < Range("A" & lngRow) Then varAccNumber = Range("A" & lngRow) lngRow = lngRow + 1 Rows(lngRow).Insert Range("A" & lngRow & ":C" & lngRow).Interior.Color = vbYellow End If Next End Sub If this post helps click Yes --------------- Jacob Skaria Hi: I am trying to format an excel worksheet based on acount number changing [quoted text clipped - 12 lines] 5678 12/31/2009 4.59 hightlight here -- Message posted via http://www.officekb.com |
Exce Formating
Dear Laura
What Rick pointed out is true. The way you have posted your example is misleading and whoever answer the post might assume things.. Sometimes it work but most of the times it may not. When you post examples please give enough description so as to be specific. Now for your query. To color the entire row replace the below line with Range("A" & lngRow & ":C" & lngRow).Interior.Color = vbYellow Rows(lngRow).Interior.Color = vbYellow If this post helps click Yes --------------- Jacob Skaria "Rick Rothstein" wrote: Jacob has given you an answer and it may be what you were after, but I found your question to be unclear. What did you mean be "highlight here"... you show several blanks rows after it and is not clear to me if that is what you want to insert or if you had something else in mind for your "highlight". Can you clarify how you want your data to look afterwards? -- Rick (MVP - Excel) "Laura1 via OfficeKB.com" <u31091@uwe wrote in message news:957aaf4cc0628@uwe... Hi: I am trying to format an excel worksheet based on acount number changing I have 3000 records and want the row to change with the account number changes. any ideas? Example: Account Number Date: Amount: 1234 12/31/2009 1.20 1234 12/31/2009 2.29 1234 12/31/2009 3.40 highlight here 5678 12/31/2009 4.59 5678 12/31/2009 4.59 hightlight here -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200905/1 |
Exce Formating
Thank hyou again, sorry about not providing enough information I thought I
was, I will be sure to add additional information, problem is I didn't know it mattered if the accounts were in order, so it wasn't a delibrate omition but rather a lack of knowledge of what was needed. Next time I will provide more... Thanks again! Jacob Skaria wrote: Dear Laura What Rick pointed out is true. The way you have posted your example is misleading and whoever answer the post might assume things.. Sometimes it work but most of the times it may not. When you post examples please give enough description so as to be specific. Now for your query. To color the entire row replace the below line with Range("A" & lngRow & ":C" & lngRow).Interior.Color = vbYellow Rows(lngRow).Interior.Color = vbYellow If this post helps click Yes --------------- Jacob Skaria Jacob has given you an answer and it may be what you were after, but I found your question to be unclear. What did you mean be "highlight here"... you [quoted text clipped - 19 lines] 5678 12/31/2009 4.59 hightlight here -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200905/1 |
| All times are GMT +1. The time now is 05:43 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com