Exce Formating

[Laura1 via OfficeKB.com]

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

 

[Jacob Skaria]

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

 

[Rick Rothstein]

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?

 

[Laura1 via OfficeKB.com]

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!

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

 

[Laura1 via OfficeKB.com]

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!

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

 

[Rick Rothstein]

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)

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!

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

 

[Jacob Skaria]

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
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?

 

[Laura1 via OfficeKB.com]

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!

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]