Thread: Shade rows A:J
View Single Post
  #8   Report Post  
Posted to microsoft.public.excel.programming
Elaine Elaine is offline
external usenet poster
 
Posts: 106
Default Shade rows A:J

Thanks, Tom. I saw Jim's comment and changed it to 1500. I might at the most
have 1200 records.

Thanks to you and Jim for your help and explanations as well.

"Tom Ogilvy" wrote:

You wrote your code
For lngCounter = 1337 To 1 Step -1

but said:
if current cell has the word total and the cell above has the word total in
it,

so I looked up (from row 100 to row 99) to check for the other cell with
total.

when it reaches 1 in you loop, it can't look up. So you would modify

For lngCounter = 1337 To 2 Step -1

to address Jim's concern, if you have more rows, change 1337 to the number
of rows. That was your design criteria - not mine.

--
Regards,
Tom Ogilvy


"Elaine" wrote in message
...
Thank you very much for your reply. I tried all three -- including Jim's
follow up. I was curious about Tom's code though and I was wondering if I

had
to have declaration at the top of the module or add something to the

library.

Tom's code runs and shades the rows correctly but I get an
Application-defined or Object-Defined error. (Run time error 1004).

This following line is highlighed in yellow and I get the debug dialog

box.
If Application.CountIf(RngCell.Offset(-1, 0).Resize(2), "*total*") = 2

Then

When I pass my mouse over the highlighted line it reads:
RngCell.offset(-1,0).Resize(2) = <Application-defined or object-defined

error

Any ideas on what causes this?

I was in cell A6 when I ran the macro.
The only declaration at the top of the module is option compare text.

"Tom Ogilvy" wrote:

Tom's is great, but it doesn't work if you have more than 1337 rows of
data.

Why would that be Jim?

--
Regards,
Tom Ogilvy



"Jim Thomlinson" wrote in

message
...
Here is some code that works no matter how many rows of data you have.
Tom's
is great, but it doesn't work if you have more than 1337 rows of data.
Depends what you need.

Public Sub Shade()
Dim strFirstAddress As String
Dim rngFound As Range
Dim rngToSearch As Range

Set rngToSearch = ActiveSheet.Range("E1").EntireColumn

Set rngFound = rngToSearch.Find("total", , , xlPart)
If rngFound Is Nothing Then
MsgBox "No areas to shade."
Else
strFirstAddress = rngFound.Address
Do
If rngFound.Offset(1, 0).Value Like "*total*" Then
Range(rngFound.Offset(2, -4), rngFound.Offset(2,
5)).Interior.ColorIndex = 35
End If
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = strFirstAddress
End If
End Sub

"Elaine" wrote:

I have an Excel 2002 spreadsheet where there are several records. I
would
like to shade certain rows from Col A: Col J where the following

occurs:
if
current cell has the word total and the cell above has the word

total in
it,
I would like to shade two rows below current cell (with say green --
code 35).

Thus, if E42 = Book Total and E43 is June Total, I would like to

shade
A:J
in row 45.

If E42 = Book Total and E43 does not contain total then do nothing.

Tom Ogilvy and Jim Cone helped me in a previous problem similar to

this
but
I am stumped and don't know how to proceed from the point below. Any
help is
really appreciated. Thanks.

Private Sub mcr18Shade()

Dim RngCell As Range
Dim lngCounter As Long

For lngCounter = 1337 To 1 Step -1
Set RngCell = ActiveSheet.Cells(lngCounter, 5) 'Col E

''If current cell in E has word total in it and cell above has total

in
it
then shade A:J

If RngCell.Value Like "*total*" And _
InStr(1, LCase(RngCell.Offset(-1, 0).Value), "total") 0

Then

'Shade two rows below green (interior.color=35)
End If
Next
End Sub