| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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.) |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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.) |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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.) |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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.) |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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.) |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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.) |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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.) |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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.) |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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.) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Deselect B1 & move curser to select A1 | Excel Discussion (Misc queries) | |||
| Select & Move Range | Excel Discussion (Misc queries) | |||
| Macro to select cells without a certain value and select a menu it | Excel Worksheet Functions | |||
| How do I select all the even rows in a worksheet and move them. | Excel Discussion (Misc queries) | |||
| Move select data to another worksheet | Excel Discussion (Misc queries) | |||
Linear Mode
