D
Dave Birley
I'm Baaaack <g>! I hacked on this li'l dude for a couple of hours yesterday,
and it still won't behave. Here it is as far as i have gone with it:
Sub TryNow()
Dim myCount As Integer
Dim StartRow As Long
Dim myCell As Range
myCount = 0
For Each myCell In Range("C2:C53")
StartRow = myCell.Row
If myCell.Value <> myCell(2, 1).Value Then
If myCell(2, 9).Value <> "PEN-Pensionable Earnings" Then
myCount = 1
While myCell(myCount + 1, 1).Value = myCell(myCount + 2, 1)
myCount = myCount + 1
Wend
StartRow = StartRow + myCount - 3
Selection.End(xlToLeft).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(("A" & StartRow), ("Q" & StartRow + myCount - 1)).Select
With Selection.Interior
.ColorIndex = 34
.Pattern = xlSolid
End With
myCount = 0
Else
myCount = 0
End If
Else
myCount = myCount + 1
End If
Next myCell
End Sub
Column C contains SSNs, on which the data is sorted. Column K (myCell(2,9)
contains a variety of terms which identify the type of numeric data. One of
the terms associated with most of the people is "PEN-Pensionable Earnings".
If a person does NOT have that in the first row, then he/she does not have it
at all.
I need to highlight all the rows of those folks without the Pensionable
Earnings and change the background color. It seems to work with certain
numbers of rows after the first, and then grab the wrong row as the first row
with a different number of rows. Individuals could have anywhere from two to
ten rows total each, hence the counter.
Once I have done all that, I will return to the data with a second macro
that will find all the folks WITH the Pensionable Earnings item, add up all
the rows following that item (from Column J) and compare the total of those
to the value in Column J on the Pensionable Earnings row. If they do not
match I will highlight those rows in a different background color, as well as
printing out the total in column AA.
Dang, it sounds complex, but it shouldn't be as difficult as I am finding
it. I would have no problem writing something to do this if I were working in
VFP -- but I'm not, am I? <g>!
Anyone feel like jumping in and taking a whack at this?
and it still won't behave. Here it is as far as i have gone with it:
Sub TryNow()
Dim myCount As Integer
Dim StartRow As Long
Dim myCell As Range
myCount = 0
For Each myCell In Range("C2:C53")
StartRow = myCell.Row
If myCell.Value <> myCell(2, 1).Value Then
If myCell(2, 9).Value <> "PEN-Pensionable Earnings" Then
myCount = 1
While myCell(myCount + 1, 1).Value = myCell(myCount + 2, 1)
myCount = myCount + 1
Wend
StartRow = StartRow + myCount - 3
Selection.End(xlToLeft).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(("A" & StartRow), ("Q" & StartRow + myCount - 1)).Select
With Selection.Interior
.ColorIndex = 34
.Pattern = xlSolid
End With
myCount = 0
Else
myCount = 0
End If
Else
myCount = myCount + 1
End If
Next myCell
End Sub
Column C contains SSNs, on which the data is sorted. Column K (myCell(2,9)
contains a variety of terms which identify the type of numeric data. One of
the terms associated with most of the people is "PEN-Pensionable Earnings".
If a person does NOT have that in the first row, then he/she does not have it
at all.
I need to highlight all the rows of those folks without the Pensionable
Earnings and change the background color. It seems to work with certain
numbers of rows after the first, and then grab the wrong row as the first row
with a different number of rows. Individuals could have anywhere from two to
ten rows total each, hence the counter.
Once I have done all that, I will return to the data with a second macro
that will find all the folks WITH the Pensionable Earnings item, add up all
the rows following that item (from Column J) and compare the total of those
to the value in Column J on the Pensionable Earnings row. If they do not
match I will highlight those rows in a different background color, as well as
printing out the total in column AA.
Dang, it sounds complex, but it shouldn't be as difficult as I am finding
it. I would have no problem writing something to do this if I were working in
VFP -- but I'm not, am I? <g>!
Anyone feel like jumping in and taking a whack at this?