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
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
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:
Upvote 0
I don't believe it :mad:

Here i spent hours Searching and trying different codes.

Just had the ranges reversed. :(

Thanks Mike. ;)
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,916
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top