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

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
24,022
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,133,324
Messages
5,658,155
Members
418,430
Latest member
Kizega

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
Top