|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
October 9th, 2009, 03:22 PM
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
October 9th, 2009, 03:31 PM
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
October 9th, 2009, 03:57 PM
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
October 9th, 2009, 04:00 PM
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
October 9th, 2009, 04:07 PM
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
October 9th, 2009, 06:40 PM
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
October 9th, 2009, 07:21 PM
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
October 9th, 2009, 07:41 PM
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
October 9th, 2009, 09:34 PM
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.)
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
