Find Cell at intersection

Nalani

Well-known Member
Joined
Apr 10, 2009
Messages
1,047
I've been trying with little luck to find the cell, or better yet Activate it so I can do something once found.

With the below:
Back Country

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 51px"><COL style="WIDTH: 51px"><COL style="WIDTH: 120px"><COL style="WIDTH: 81px"><COL style="WIDTH: 70px"><COL style="WIDTH: 70px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="FONT-FAMILY: Calibri; FONT-SIZE: 11pt"> </TD><TD style="FONT-FAMILY: Calibri; FONT-SIZE: 11pt"> </TD><TD style="FONT-FAMILY: Calibri; FONT-SIZE: 11pt"> </TD><TD style="FONT-FAMILY: Calibri; FONT-SIZE: 11pt; FONT-WEIGHT: bold">Driveway</TD><TD style="FONT-FAMILY: Calibri; FONT-SIZE: 11pt; FONT-WEIGHT: bold">Options</TD><TD style="FONT-FAMILY: Calibri; FONT-SIZE: 11pt; FONT-WEIGHT: bold">Complete</TD></TR><TR style="HEIGHT: 62px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Blk/Lot</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Model</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Calibri; FONT-SIZE: 11pt; FONT-WEIGHT: bold">Options</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Calibri; FONT-SIZE: 11pt; FONT-WEIGHT: bold">25030</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Calibri; FONT-SIZE: 11pt; FONT-WEIGHT: bold">25030</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: Calibri; FONT-SIZE: 11pt; FONT-WEIGHT: bold">25030</TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Calibri; FONT-SIZE: 11pt">65</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Calibri; FONT-SIZE: 11pt">C270</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Calibri; FONT-SIZE: 11pt">C, CG3T</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Calibri; FONT-SIZE: 11pt">$956.25</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Calibri; FONT-SIZE: 11pt">$0.00</TD><TD style="BACKGROUND-COLOR: #ffff00; FONT-FAMILY: Calibri; FONT-SIZE: 11pt"> </TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Calibri; FONT-SIZE: 11pt">66</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Calibri; FONT-SIZE: 11pt">C239</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Calibri; FONT-SIZE: 11pt">B</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Calibri; FONT-SIZE: 11pt">$929.25</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Calibri; FONT-SIZE: 11pt">$0.00</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: Calibri; FONT-SIZE: 11pt">6/11/10</TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Calibri; FONT-SIZE: 11pt">67</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Calibri; FONT-SIZE: 11pt">C265</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Calibri; FONT-SIZE: 11pt">AG3T</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ff0000; FONT-FAMILY: Calibri; FONT-SIZE: 11pt">$1,017.00</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ff0000; FONT-FAMILY: Calibri; FONT-SIZE: 11pt">$0.00</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ff0000; FONT-FAMILY: Calibri; FONT-SIZE: 11pt">4/29/10</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

I am tring to find cell G6 wich is the intersection of G4 and B6. Once found then I will change the cell color to RED for E6:G6 if there is a date in G6. Similar to what row 7 is. This I am trying to do from a UserForm .

This is the code that I have so far:
Code:
Private Sub CommandButton1_Click()
Dim myCode As String
Dim myLot As String
Dim myCol As Range
Dim myRow As Range
 
 ActiveWorkbook.Sheets(ComboBox1.Value).Activate
 
myCode = Me.ComboBox3.Value
myLot = Me.TextBox1.Value
 
Set myRow = Rows(4).Find(myCode).Offset(, 2)
Set myCol = Columns(2).Find(myLot)
'x = myRow.Address
'y = myCol.Address
Intersect(myRow.EntireRow, myCol.EntireColumn).Activate
Unload Me
End Sub
As is, the code does find G4 and B6 but the cursor goes to B4. The x & y lines there confirms the findings and I thought that I may need the Address to do the Intersect.

I have tried to put the x & y in the Intersect function, but can't get it to work. Probably just screwing up on the syntax, don't know.

A little guidance would be appreciated.
Thanks
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,766
try
Code:
Intersect(myRow.EntireColumn, myCol.EntireRow).Activate
Consider, since myRow = myRow = Rows(4).Find(myCode).Offset(, 2),
myRow.EntireRow will always = Rows(4), no matter what the value of myCode is.

However myRow.EntireColumn will vary with a change to myCode.
 
Last edited:

Nalani

Well-known Member
Joined
Apr 10, 2009
Messages
1,047
I don't believe it :mad:

Here i spent hours Searching and trying different codes.

Just had the ranges reversed. :(

Thanks Mike. ;)
 

Nalani

Well-known Member
Joined
Apr 10, 2009
Messages
1,047
Just seen your Edit, Mike

Yes that is true. There will be changes to myCode depending on what's chosen in ComboBox3.

Tested with another Value which is in column P (same Lot #), works fine.
Excel Workbook
BCDNOP
4Blk/LotModelOptions25055 Porch25055 Porch Option25055 Complete
565C270C, CG3T$1,625.50$85.006/11/10
666C239B$1,602.25$350.006/11/10
Back Country


Cursor went to P6.

Thanks again.
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,105,944
Messages
5,508,288
Members
408,673
Latest member
CELER_

This Week's Hot Topics

Top