Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto select rows
I am using the following code to find "mySite1" which is always in column A.
Cells.Find(What:=mySite1, After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate Once "mySite1" is found, I need the code to highlight from the selected, found cell, including that cell, over to the right for a total of 22 cells. So for example, if my code finds "mySite1" at A5, I need the code to highlight A5 to V5. If my code finds "mySite1" at A7, I need the code to highlight A7 to V7, 22 cells total. Because "mySite1" can be on a different row from one report to another, I can not specify the actual cells, ie A5:V5. I tried using this code after "mySite1" is found but it does not work if any cells between column A & V are blank even if I use the code multiple times. Range(Selection, Selection.End(xlToRight)).Select How can I write code to select a number of cells without specifying cells like A5:V5? I need to find "mySite1" then highlight from the found cell in column A to column V same row # as the found cell. Any help is appreciated. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto select rows
use something like this, but you'll have to change some of the code because i
don't have the formula you use. i just used the line mysite1 = "test1" to test this. Sub test() Dim ws As Worksheet Dim mysite1 As String Dim rngfound As Range Set ws = Worksheets("Sheet1") mysite1 = "test1" With ws.Columns("A:A") Set rngfound = .Find(What:=mysite1, After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False) If Not rngfound Is Nothing Then ws.Range("A" & rngfound.Row & ":V" & rngfound.Row).Interior.ColorIndex = 17 End If End With End Sub -- Gary "SJW_OST" wrote in message ... I am using the following code to find "mySite1" which is always in column A. Cells.Find(What:=mySite1, After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate Once "mySite1" is found, I need the code to highlight from the selected, found cell, including that cell, over to the right for a total of 22 cells. So for example, if my code finds "mySite1" at A5, I need the code to highlight A5 to V5. If my code finds "mySite1" at A7, I need the code to highlight A7 to V7, 22 cells total. Because "mySite1" can be on a different row from one report to another, I can not specify the actual cells, ie A5:V5. I tried using this code after "mySite1" is found but it does not work if any cells between column A & V are blank even if I use the code multiple times. Range(Selection, Selection.End(xlToRight)).Select How can I write code to select a number of cells without specifying cells like A5:V5? I need to find "mySite1" then highlight from the found cell in column A to column V same row # as the found cell. Any help is appreciated. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto select rows
You are great! Thank you very much.
I just needed to tweek 1 part. I mis-stated myself, I needed the code to select the cells instead of highlight. I made that change and it works great. Thank you! "Gary Keramidas" wrote: use something like this, but you'll have to change some of the code because i don't have the formula you use. i just used the line mysite1 = "test1" to test this. Sub test() Dim ws As Worksheet Dim mysite1 As String Dim rngfound As Range Set ws = Worksheets("Sheet1") mysite1 = "test1" With ws.Columns("A:A") Set rngfound = .Find(What:=mysite1, After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False) If Not rngfound Is Nothing Then ws.Range("A" & rngfound.Row & ":V" & rngfound.Row).Interior.ColorIndex = 17 End If End With End Sub -- Gary "SJW_OST" wrote in message ... I am using the following code to find "mySite1" which is always in column A. Cells.Find(What:=mySite1, After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate Once "mySite1" is found, I need the code to highlight from the selected, found cell, including that cell, over to the right for a total of 22 cells. So for example, if my code finds "mySite1" at A5, I need the code to highlight A5 to V5. If my code finds "mySite1" at A7, I need the code to highlight A7 to V7, 22 cells total. Because "mySite1" can be on a different row from one report to another, I can not specify the actual cells, ie A5:V5. I tried using this code after "mySite1" is found but it does not work if any cells between column A & V are blank even if I use the code multiple times. Range(Selection, Selection.End(xlToRight)).Select How can I write code to select a number of cells without specifying cells like A5:V5? I need to find "mySite1" then highlight from the found cell in column A to column V same row # as the found cell. Any help is appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can i randomly select 780 rows from 4000 rows of data | Excel Worksheet Functions | |||
Auto Number the Rows of Auto Filter Result | Excel Discussion (Misc queries) | |||
select block of rows w/data between blank rows | Excel Programming | |||
Unable to select rows in the repeat rows on top option | Excel Discussion (Misc queries) | |||
Auto filling a select # of rows | Excel Programming |