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

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.)

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

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
---------------
Jacob Skaria


"Karin" wrote:

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.)

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

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

--
HTH...

Jim Thomlinson


"Karin" wrote:

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.)

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

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.
--
HTH...

Jim Thomlinson


"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"Karin" wrote:

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.)

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

Thanks Jim for pointing out those..


"Jim Thomlinson" wrote:

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.
--
HTH...

Jim Thomlinson


"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"Karin" wrote:

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.)

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

It is only coloring cell K3

"Jim Thomlinson" wrote:

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

--
HTH...

Jim Thomlinson


"Karin" wrote:

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.)

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

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


--
HTH...

Jim Thomlinson


"Karin" wrote:

It is only coloring cell K3

"Jim Thomlinson" wrote:

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

--
HTH...

Jim Thomlinson


"Karin" wrote:

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.)

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

$T$3

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



"Jim Thomlinson" wrote:

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


--
HTH...

Jim Thomlinson


"Karin" wrote:

It is only coloring cell K3

"Jim Thomlinson" wrote:

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

--
HTH...

Jim Thomlinson


"Karin" wrote:

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.)

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

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!

"Karin" wrote:

$T$3

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



"Jim Thomlinson" wrote:

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


--
HTH...

Jim Thomlinson


"Karin" wrote:

It is only coloring cell K3

"Jim Thomlinson" wrote:

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

--
HTH...

Jim Thomlinson


"Karin" wrote:

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.)