Macro to Return Row, then move over and select up to

[Karin]

Hi, (Excel 2003) I'm writing a macro to format a report. I want to Find
"Grand Total" and then move over to column K, highlight up to K3 (absolute)
and fill with gray. How do I do that? Thank you. (FYI: The length of the
report will change, and Ctrl End actually goes past the end of the report.)

 

[Jacob Skaria]

Try the below macro which will work on the active sheet...

Sub Macro()

Dim rngTemp As Variant
Set rngTemp = Cells.Find("Grand Total")
If Not rngTemp Is Nothing Then
'highlight from the cell to k3
Range("K3", rngTemp).Interior.ColorIndex = 15

'or if you are looking to highlight only col K
'Range("K3:K", rngTemp).Interior.ColorIndex = 15
End If

If this post helps click Yes

 

[Jim Thomlinson]

Public Sub FormatTotal()
Dim wks As Worksheet
Dim rngFound As Range

Set wks = ActiveSheet
Set rngFound = wks.Cells.Find(What:="Grand Total", _
LookAt:=xlWhole, _
LookIn:=xlFormulas, _
MatchCase:=True)

If rngFound Is Nothing Then
MsgBox "Grand Total Not Found"
Else
With wks
.Range(.Range("K3"), .Cells(rngFound.Row, "K")).Interior.ColorIndex
= 15

End With
End If
End Sub

 

[Jim Thomlinson]

The line Set rngTemp = Cells.Find("Grand Total") may or may not find the cell
depending on the current find settings which you can not know. Also the line
'Range("K3:K", rngTemp).Interior.ColorIndex = 15
will not work...
'Range("K3:K" & rngTemp.row).Interior.ColorIndex = 15
would be better.

 

[Jacob Skaria]

Thanks Jim for pointing out those..

The line Set rngTemp = Cells.Find("Grand Total") may or may not find the cell
depending on the current find settings which you can not know. Also the line
'Range("K3:K", rngTemp).Interior.ColorIndex = 15
will not work...
'Range("K3:K" & rngTemp.row).Interior.ColorIndex = 15
would be better.

 

[Karin]

It is only coloring cell K3

Public Sub FormatTotal()
Dim wks As Worksheet
Dim rngFound As Range

Set wks = ActiveSheet
Set rngFound = wks.Cells.Find(What:="Grand Total", _
LookAt:=xlWhole, _
LookIn:=xlFormulas, _
MatchCase:=True)

If rngFound Is Nothing Then
MsgBox "Grand Total Not Found"
Else
With wks
.Range(.Range("K3"), .Cells(rngFound.Row, "K")).Interior.ColorIndex
= 15

End With
End If
End Sub

 

[Jim Thomlinson]

Try this and let me know what address pops up in the message box...

Public Sub FormatTotal()
Dim wks As Worksheet
Dim rngFound As Range

Set wks = ActiveSheet
Set rngFound = wks.Cells.Find(What:="Grand Total", _
LookAt:=xlWhole, _
LookIn:=xlFormulas, _
MatchCase:=True)

If rngFound Is Nothing Then
MsgBox "Grand Total Not Found"
Else
With wks
msgbox rngfound.address
.Range(.Range("K3"), .Cells(rngFound.Row, "K")).Interior.ColorIndex
= 15

End With
End If
End Sub

 

[Karin]

$T$3

FYI: Grand Total is in A13 (should always be in A)

 

[Karin]

MY BAD!!! There was another Grand Total in the report that was in White and
I couldn't see it! (For what it's worth, I didn't create the report, just
trying to help auto format it.)
It works.

Thank you very much for your help!