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

  • Thread starter Thread starter Karin
  • Start date Start date
K

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.)
 
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
 
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
 
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.
 
Thanks Jim for pointing out those..


Jim Thomlinson said:
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.
Click to expand...
 
It is only coloring cell K3

Jim Thomlinson said:
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
Click to expand...
 
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
 
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!
 
Back
Top