hyperlink parents. (excel 2000)

Topic

New Reply

I have two columns, Parent P/N and BOM P/N(child). What I need to do is find every relative that is associated with either the parent or child and Highlight them. I have attached a sample of the situation. Please not the one in red and follow it. It will show exactly what I mean.

Reply | Quote

Replies

Why not the one in red?

Try the following code. The first procedure is the macro to run.

Private lngMaxRow As Long

Sub Hilite()
If ActiveCell.Column > 1 Then
MsgBox “Please select a cell in column A.”, vbExclamation
Exit Sub
End If
lngMaxRow = Range(“A65536″).End(xlUp).Row
If ActiveCell.Row = 1 Or ActiveCell.Row > lngMaxRow Then
MsgBox ” Please select a cell within the range.”, vbExclamation
Exit Sub
End If
Range(“A2:B” & lngMaxRow).Font.ColorIndex = xlColorIndexAutomatic
HiliteMeAndKids ActiveCell
End Sub

Sub HiliteMeAndKids(oCell As Range)
Dim i As Long
oCell.Resize(1, 2).Font.ColorIndex = 3
For i = 2 To lngMaxRow
If Range(“A” & i) = oCell.Offset(0, 1) Then
HiliteMeAndKids Range(“A” & i)
End If
Next i
End Sub

Reply | Quote

What do you mean why not the one in red? the PR-05400 is the child I’m trying to follow. But he can also be a parent as well. If he is a parent I need to know any and all other children.

Reply | Quote

You wrote “Please not the one in red”. Do you have a special aversion to red?

Reply | Quote

My deepest apologies, I’m not awake yet. That was supposed to read ” Please Note the one in red” so that somebody could foolow if fron the BOM (Child) column to the parent column.

Reply | Quote

Still not quite awake?

Try the following:

Private lngMaxRow As Long

Sub Hilite()
If ActiveCell.Column 2 Then
MsgBox “Please select a cell in column B.”, vbExclamation
Exit Sub
End If
lngMaxRow = Range(“A65536″).End(xlUp).Row
If ActiveCell.Row = 1 Or ActiveCell.Row > lngMaxRow Then
MsgBox ” Please select a cell within the range.”, vbExclamation
Exit Sub
End If
Range(“A2:B” & lngMaxRow).Font.ColorIndex = xlColorIndexAutomatic
HiliteMeAndSiblings ActiveCell
End Sub

Sub HiliteMeAndSiblings(oCell As Range)
Dim i As Long
oCell.Offset(0, -1).Resize(1, 2).Font.ColorIndex = 3
For i = 2 To lngMaxRow
Debug.Print i
If Range(“A” & i) = oCell Then
HiliteMeAndSiblings Range(“B” & i)
End If
Next i
End Sub

Reply | Quote

Ok, I’m back among the living. Hans, My apologies for yesterday I really was not with it at all. Please let me explain again what I’m trying to do here.

The two columns are Column “H” and “I” what I’m trying to do is if the user clicks on a child in column “I”, I would like for them to be able to able to see any and all items that are associated with it. whether it be a parent or child. The same goes for if the user clicks on a cell in the parent column. Is there a way to have the user Hyperlink to the next listing of the same item or to have a msgbox appear that shows all the items both parent and child as well as the Job number COLUMN “F” show.

My main objective here is to make the user aware of any and all items associated with that particular Item as well as the job numbers.

Reply | Quote

Hello, one and all.

I stated before that I did not understand what the instructions implied, Mainly because I did not understand what was expected.

I informed my teacher at school and he informed me that I need to review the book a little more, well I did and still don’t. Sometimes it just takes a while to click I guess.

What is expected: Column “I” is the part needed to create the Part in Column “H” but the part in column “H” could also be waiting on another part to be created in Column “I”. When all parts are in or have been created the Job can be released which means that the cell in column “B” turns Green.
if only a partial order has been met (Not all parts are there or they still need to be created) then cell in column “B” is yellow. Otherwise cell in column “B” is blue. I also need to know if a PO number Column “w” is holding up the process as well. last thing I’m trying to accomplish is a message box that tells the user what is keeping the job from being created.

Not that easy HUH?

There it is folks. and no I do not want anybody to complete this for me. What I’m asking for a a little guidance. because if you complete it for me I will not be learning. Correct. Although I have been trying to break down what some the the programmers here are writing. Sometimes I understand it sometimes I dont. Thats when I ask for a simplier understanding of what a certain program does.

thank you Gentleman for Understanding and helping.

Reply | Quote

Ok with that any idea’s On how to even start this? Anybody?

Reply | Quote

> When all parts are in or have been created

How can we know that a part is in or has been created? Is this specified somewhere in the spreadsheet? (Please give a clear and complete answer)

> .. a PO number Column “w” …

What does that mean? (Please give a clear and complete answer)

Reply | Quote

I will try Hans, and thank you SOOOO much for getting back with me.

It works like this. Column “H” has all the part names within it. (A total of 233 parts). While Column “I” has Parts in it as well. Now lets say that we are trying to make a part number Called for the sake of Argument “PART D”. Part “D” would be found in Column H – but to build part D we need Parts A,B & C. Which would be found in Column I.

Now we Need to Make Part E, which also is in column H. But Part E is made up of parts A,B,C & D. which are found in column I and so on.

I need to be able to locate every reference of the Part number I’m looking for whether it be in column H or column I. If I click on a part number I should be able to notice all the other parts that are waiting on the part I need and to be able to see if a PO Number is holding it up. Thats exactly what the teacher said.

You see the main part that is found in column H can also be a smaller part (Column I) used to make another main part in column H.

Then I have to check Column W (the same row that the part is found on) to ensure it is blanks if not then I need to have a Popup message that states ” Part is waiting on PO Number (then the number that was found in that cell).

This one is very very Hard, It’s not easy going back and fourth from excel to vb to excel/vb together. I hope that helps – copied it down word for word.

My problem is I don’t really know the shortcuts in VB/Excel yet and keep wanting to use IF statements.

Reply | Quote

But your example has none of the info required. Could you provide an example of what you have and what you need?

how do you know whether the parts are available or not?

What is in all those “new” columns that you are referring to?

Steve

Reply | Quote

Here is the spreedsheet info we were given.

If the parts are not available there would be a PO number in Column W.

What we are trying to accomplish is given the info in column H, I and W. The user needs to know what the part is being held up by – whether its another part thats waiting on something else or is that part waiting on something that was purchased (PO Number). And they need to be able to see it clearly. So I suggested a Message box and he said that was ok.

Reply | Quote

If this is class assignment shouldn’t you be doing this on your own? If we tell you how to do this, how does this help you learn? I suppose we can offer some suggestions and let you do the work.

It is still not clear to me what you want and need. The last few descriptions have dealt with H, I, and W. How do A, B, C, D, E, and X work into it? Also why are some of the items in col A blank? and why do none of the columns have descriptive labels?

Can you give some examples, based on this sample, of what you want the workbook to do?

Steve

Reply | Quote

That’s exactly what I’m looking for. Thats the reason why I have not been spelling it out directly to everybody so that I can monkey around with what you think I should do and try to figure it out on my own. As for the other columns They are being used by other data as well It’s just that that data does not matter in the equation.

Right now I have it set up so that if a part in column “I” is selected any parents it belongs that are associated with it in any way are highlighted. (Thanks to Hans, and a little tweaking). I’m hoping that some how I can set it up to include the PO Number (COLUMN W) within the equation and the give a message to the user stating ” The part you are looking for is waiting on part number……whatever, or a PO Number … But I’m not sure how to go about doing that.

Please review the attachment and look at column “I” row 21 you will see number PR-05399
If you now look at Column “H” row 18 you will see it’s parent. but that parent is waiting on another part CNB42.079X6-G.
I would also need to check Column W (Po #) to see if it’s waiting on a purchased part or not.

I have my code listed below but it works on every column. By that I mean If I click on a cell in column b anything in column A that resembles it would be hilited as well. I only want this to work on columns H and I.

Plus How do I go about creatinmg the pop up message if the main parent is waiting on a PO # or another part.

I’m Lost totally… And the teacher has already said he will not assist. This has to be done by the end of spring break in two weeks.

Private lngMaxRow As Long

Private Sub EXEC_BTN_Click()
lngMaxRow = Range(“I65536”).End(xlUp).Row
Range(“H2:I” & lngMaxRow).Font.ColorIndex = xlColorIndexAutomatic
HiliteMeAndSiblings ActiveCell
End Sub

Sub HiliteMeAndSiblings(oCell As Range)
Dim i As Long
oCell.Offset(0, -1).Resize(1, 2).Font.ColorIndex = 5
For i = 2 To lngMaxRow
Debug.Print i
If Range(“H” & i) = oCell Then
HiliteMeAndSiblings Range(“I” & i)
End If
Next i
End Sub

Reply | Quote

Better yet is there a way to hide any other rows that do not pertain to the hi-lited areas automatically. If so how would that be done.

Reply | Quote

You can modify the code to work on the non-matching rather than the matching cells or hide all the rows then unhide active row and the rows matching rather than coloring the cells.

Steve

Reply | Quote

I would like rows where I find any reference or child/parent of what I’m looking for to show automatilcally only. another words hide all the other rows that I do not need.

Reply | Quote

Yes the code can be modified to do that, as I described before. You have code to find them all, it just needs tweaking to do something other than highlighting them.

but once the rows are hidden. you will need a button to unhide them all so you can select a different one and run the code.

Steve

Reply | Quote

Ok, I spent most of my weekend looking through the book, It does not state anywhere what kind of code I need to use to have the rows hide or unhide. Besides doing it manually. What I’m looking for is when the Parent and Children are all selected I need only those rows to appear. and when the user clicks on them again all rows appear again. I need help on this anybody. Please.

Reply | Quote

This will hide all the rows in the range
Range(“H4:I” & lngMaxRow).EntireRow.Hidden = True

This will unhide all the rows
Range(“H4:I” & lngMaxRow).EntireRow.Hidden = False

This line in “HiliteMeAndSiblings” will unhide the “matching row” rather than coloring it:
oCell.Offset(0, -1).EntireRow.Hidden = False

You can run the code to hide all rows, then have it go thru the “HiliteMeAndSiblings” (you could change the name to HideMeAndSiblings” if desired).

Then have a button to unhide all the rows so you can select a new cell and then run the routine to hide based on that one.

Steve

Reply | Quote

So where would I place them at in my code. I tried and got an error saying subscript out of range.

Private lngMaxRow As Long

Private Sub EXEC_BTN_Click()

lngMaxRow = Range(“I65536”).End(xlUp).Row
Range(“H2:I” & lngMaxRow).Font.ColorIndex = xlColorIndexAutomatic
HiliteMeAndSiblings ActiveCell
End Sub

Sub HiliteMeAndSiblings(oCell As Range)
Dim i As Long
oCell.Offset(0, -1).Resize(1, 2).Font.ColorIndex = 5
For i = 2 To lngMaxRow
Debug.Print i
If Range(“H” & i) = oCell Then
HiliteMeAndSiblings Range(“I” & i)
End If
Next i
End Sub

Reply | Quote

I think I figured it out. Thank you so much. Now one last question. Is there a way to speed up that process. Because I have over 800 rows of data and it takes a few minutes for it to actually work. If not I will take what I can get. Thank you SOOOOOO much once again.

Reply | Quote

OK something went wrong. It’s only showing the row that I clicked on and not all parents and children in any other rows.

Reply | Quote

Where did you put the lines of code and what did you use?

You can have them replace the lines which currently color.

I would first unhide all the rows then check the “lastrow”. If the selection is not in H or I or is blank I would just unhide all and not hide anything (running with a “blank cell” is then used to display all). If it is in H/I and not blank then it hide all, and run the unhide routine.

To speed it up, instead of doing each unhiding separately, you might want to create “unhide range” object making each new row UNION to the current range (expanding it as you go). Then just unhide this range object all at once.

Steve

Reply | Quote

that sounds great, But remember one thing Newbie here. I’m still in school learning this stuff.

Reply | Quote

This is what I have so far.
Private lngMaxRow As Long

Private Sub EXEC_BTN_Click()

lngMaxRow = Range(“I65536”).End(xlUp).Row
Range(“H2:I” & lngMaxRow).Font.ColorIndex = xlColorIndexAutomatic
HiliteMeAndSiblings ActiveCell
End Sub

Sub HiliteMeAndSiblings(oCell As Range)
Range(“H4:I” & lngMaxRow).EntireRow.Hidden = True

Dim i As Long
‘oCell.Offset(0, -1).Resize(1, 2).Font.ColorIndex = 5
oCell.Offset(0, -1).EntireRow.Hidden = False
For i = 2 To lngMaxRow
Debug.Print i
If Range(“H” & i) = oCell Then
Range(“I” & i).EntireRow.Hidden = False
End If
Next i
End Sub

Reply | Quote

Ok I got it to actually work with the directions you stated. But now how do I bring them all back so that I can click on another Child or parent? Is there a way to make it so that I can just click on the spreedsheet anywhere and it brings them all back?

Reply | Quote

(Edited by sdckapr on 06-Mar-06 11:32. Added PS)

As I mentioned in post 561,255:

[indent]

---

I would first unhide all the rows then check the “lastrow”. If the selection is not in H or I or is blank I would just unhide all and not hide anything (running with a “blank cell” is then used to display all). If it is in H/I and not blank then it hide all, and run the unhide routine.

---

[/indent]

Unhide in the “click routine”, get the lastrow. If the intersection of activecell and columns H/I is something and the activecell is not blank, then hide all the rows and run the hiliteme with the activecell

Steve

PS. you could also do it using the “selection” event instead of your “click routine”. Unhide all rows with each selection, but if you select a cell in H/I with something in it, hide all and unhide with “hiliteme”.

Reply | Quote

ok I’m lost. I still cannot get this to bring back all my rows.

Reply | Quote

this is what I have so far:

Private Sub EXEC_BTN_click()

lngMaxRow = Range(“I65536”).End(xlUp).Row
If Range(“H2:I” & lngMaxRow) = “” Then Range(“H4:I” & lngMaxRow).EntireRow.Hidden = False
End Sub

Reply | Quote

As mentioned bring back all the rows first:

Private Sub EXEC_BTN_click()
Range(“H4:H65536”).EntireRow.Hidden = False
lngMaxRow = Range(“I65536”).End(xlUp).Row

Then check the contents of the activecell and its location (use INTERSECT). if it is not blank and in col H/I then hide all the rows and run “hilitieMe” with the activecell (hiliteme will unhide the appropriate rows)

Steve

Reply | Quote

sorry steve but INTERSECT is not in the EXCEL 2000 book. Did you mean INTERCEPT?

Reply | Quote

Intersect is not a worksheet function like SUM or VLOOKUP, but an Excel VBA function. Intersect(Range1, Range2) returns the range existing of the cells that belong to both Range1 and Range2 (their overlap).

Reply | Quote

Hi Hans did steve leave for the day? Is there anyway I can just look for the lastrow instead of going through all the rows excel has? It takes it too long to calculate everything.

Reply | Quote

I did finally get it to refresh by doing the following:

Private Sub CommandButton1_Click()
With Sheet1
ActiveWorkbook.RefreshAll
End With
End Sub

Reply | Quote

How come it does not find all the parents and children?

Reply | Quote

I don’t know what your current code is. Could you elaborate?

Steve

Reply | Quote

thanks steve, I thought you left for the day.

Private lngMaxRow As Long

Private Sub EXEC_BTN_Click()
HiliteMeAndSiblings ActiveCell
End Sub

Sub HiliteMeAndSiblings(oCell As Range)
lngMaxRow = Range(“I1000”).End(xlUp).Row

Dim i As Long
‘oCell.Offset(0, -1).Resize(1, 2).Font.ColorIndex = 5
oCell.Offset(0, -1).EntireRow.Hidden = False
For i = 9 To lngMaxRow
Debug.Print i
If ActiveCell “” Then
Range(“H5:I” & lngMaxRow).EntireRow.Hidden = True
End If
If Range(“H” & i) = oCell Then
Range(“I” & i).EntireRow.Hidden = False
End If
Next i

End Sub
Private Sub CommandButton1_Click()
With Sheet1
ActiveWorkbook.RefreshAll
End With
End Sub

Reply | Quote

i’m sorry to say this but i must leave for school now see you all later I will as always check for any updates. see you all tommorrow my friends

Reply | Quote

You have made some major changes to it and the logic is not correct

The original code Hans wrote should be the start. It was only changing the autocolor line in the click code to “hideall” and the offset color line in Hilite me to “hide the row”. Then an unhide all was added before checking for the maxrow in Click .

Hans’ code colored/uncolored the cells. You only need to modify it to hide/unhide (you will also need a trigger to unhide all, which I suggest is when the activecell is blank or not in H/I. This can be added in an IF in the click code (you would only hideall and run hiliteme if the activecell is not blank and was in Col H/I).

Why did you delete the recursive “Hiliteme” code in “hilitleme”? You need this in the code to keep looking for the parents/siblings.

Why do you have all the hiding and unhiding in the “HiliteMe” code? We talked about this one before.

There should be only 1 unhide in the hiliteme code for when it finds a match (the Offset line)

As stated previously the unhide all the rows should be the first line of CLICK code. The in the click code check the rows.

Then (in an IF if you want to check for blank and cols H/I)
hide all the rows form line 4 to lastrow
Then (also in the if) run the Hiliteme code.

If you unhide or hide other rows (other than the “matching” ones), you will not get it to work.

Steve

Reply | Quote

Sorry Steve, I was trying some things out of the book.

I have repaired it. This is what I have so Far.

Private Sub CommandButton1_Click()
With Sheet1
ActiveWorkbook.RefreshAll
End With
End Sub

Private Sub EXEC_BTN_click()
Range(“H4:H1000”).EntireRow.Hidden = False
lngMaxRow = Range(“I1000”).End(xlUp).Row
lorIndex = xlColorIndexAutomatic
HiliteMeAndSiblings ActiveCell
End Sub

Sub HiliteMeAndSiblings(oCell As Range)
Application.ScreenUpdating = False
Range(“H4:I” & lngMaxRow).EntireRow.Hidden = True

Dim i As Long
‘oCell.Offset(0, -1).Resize(1, 2).Font.ColorIndex = 5
oCell.Offset(0, -1).EntireRow.Hidden = False
For i = 2 To lngMaxRow
Debug.Print i
If Range(“H” & i) = oCell Then
Range(“I” & i).EntireRow.Hidden = False
End If
Next i
Application.ScreenUpdating = True
End Sub

Tell me Steve what would your code look like. I really do not want to fail this. But I’m just so confused between the VB books the Excel Books and what people are telling me how it should look and work. AHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHH!!!!! Real Life Programming can’t be this way, can it?

Reply | Quote

Why do you only go to row 1000? will the workbook never have more than 1000 rows of data? I would use 65536. It should not affect the speed of your code.

What is this line for?
lorIndex = xlColorIndexAutomatic

The hide all rows (as mentioned before) goes in the click event, You still have it in the “hiltieme” routine. I thought you wanted to check for blank cells before hiding all and running the “hiliteme” You do not have that code in it

I would put the screenupdating in the click event not the “hiliteme” as I mentioned before there is not need to switch it on/off dozens of times while the code runs and searches for parents/siblings. The one time in the click event start to turn off and turn it back on when the all done is sufficient.

What is the purpose of your “debug.print line” this will slow your code listing all the “matches” into the immediate pane.

If you had followed my suggestions you would have close to my code. I am reluctant to post my code, since you have not seen to even grasp the original code that Hans posted. As I stated earlier, I am not here to do your homework, my goal is to help and offer suggestions.

i am not sure Hans would have even posted that code, if he had known that he was doing homework for you. The effort on your end needs to be done to understand what the code is doing. Did you even once step thru the original code to see what it was doing so you could understand how it worked and what it did? It seems that you do not understand at all even the initial code based on how you continue to try to modify it.

I also do not know what the “commandbutton1_Click” code is for. It does not seem necessary.

I suggest you start back with hiliteme code as originally written, step thru it so you understand what it is doing. Once you understand it, read thru the posts again to understand what has been suggested and follow the directions/suggestions. Once you have grasped the original code the suggestions should make more sense and you can then ask more specific questions on your specific problems.

Steve

Reply | Quote

I told you I was a newbie….. I really suck at this thats why I’m going to school for it to hopefully get better. I really do appreciate you patience as well as your knowledge. thank you.

1). I only have 831 rows of data, I thought it would make it go faster if it did not read all the blank cells.

2). the “lorIndex = xlColorIndexAutomatic” was just a left over line from what Hans gave me I have since taken it out.

3). I have put the Hide all rows within the click event. along with the if statement.

4). I have put the screen updating within the click event as well.

5). The debug print line has been taken out. As I stated earlier I was reading from the book.

6). Hans’ code allowed me to hilite anything within columns H and I (parent and child) It looked through the whole column and if it found it it would hilight it.
Yes I agree there are some parts of this coding that is way over my head. That is the reason I ask these stupid questions. My teacher keeps telling us that in the real world people search out the answers rather through the Internet or books. I have tried to do both. It just gets so confusing day after day trying to understand what he wants out of us and what the book states to do and what the people on line tell me what to do. I really do appreciate both you and Hans for you superior Knowledge (and no I’m not a** kissing) Its true. I have stated before in the past that I have tried other sites but they are filled with Newbies like myself who really do not know what the hell they are talking about. Thats why I kep coming here. I bought the excel 2000 book and it gave me this site for reference. If I have become a Nuisance please say so and I will stop. thank you for your time and all of your efforts.

Reply | Quote

Also I have reviewed the Intersect on vba help. dont really understand how that would help. what can I say I’m an Idiot.

Returns a Range object that represents the rectangular intersection of two or more ranges.

expression.Intersect(Arg1, Arg2, …)

expression Optional. An expression that returns an Application object.

Arg1, Arg2, … Required Range. The intersecting ranges. At least two Range objects must be specified.

Example
This example selects the intersection of two named ranges, rg1 and rg2, on Sheet1. If the ranges don’t intersect, the example displays a message.

Worksheets(“Sheet1”).Activate
Set isect = Application.Intersect(Range(“rg1”), Range(“rg2”))
If isect Is Nothing Then
MsgBox “Ranges do not intersect”
Else
isect.Select
End If

My new code is below:

Private lngMaxRow As Long

Private Sub EXEC_BTN_click()
Application.ScreenUpdating = False
Range(“H4:H65536”).EntireRow.Hidden = False
lngMaxRow = Range(“I65536”).End(xlUp).Row

If ActiveCell “” Then
Range(“H4:I” & lngMaxRow).EntireRow.Hidden = True

HiliteMeAndSiblings ActiveCell
End If
Application.ScreenUpdating = True

End Sub

Sub HiliteMeAndSiblings(oCell As Range)

Dim i As Long

oCell.Offset(0, -1).EntireRow.Hidden = False
For i = 2 To lngMaxRow
Debug.Print i
If Range(“H” & i) = oCell Then
Range(“I” & i).EntireRow.Hidden = False And Range(“H” & i).EntireRow.Hidden = False
End If
Next i

End Sub

Private Sub CommandButton1_Click()
With Sheet1
ActiveWorkbook.RefreshAll
End With
End Sub

thank you gentlemen.

Reply | Quote

I told you guys this was tough. It seems that you all gave up on me.

Reply | Quote

Rule 17

Remember some of us are in different time zones and from all over the world. Steve and the others have provided a substantial amount of their time assisting you with this homework of yours. The majority of us are at work or popping into the Lounge after a long days work ans cannot specifically assist you as there are other Loungers too.

Please be patient.

Reply | Quote

Point well taken!!! My apolgies, I’m just frustrated thats all.

Reply | Quote

ok, I finally have it working where it finds everything needed except. If the cell conatins PSA-100 it finds everything that mayches it exactly. What about wildcards. How can I not only find the PSA-100’s but also the PSA-100-6 & PSA-100-msa & PSA-100klu Etc… after this I should be done.

Current code below:

rivate lngMaxRow As Long

Private Sub CommandButton1_Click()
With Sheet1
ActiveWorkbook.RefreshAll
End With
End Sub

Private Sub EXEC_BTN_click()
Application.ScreenUpdating = False

Range(“H6:H65536”).EntireRow.Hidden = False
lngMaxRow = Range(“I65536”).End(xlUp).Row

If IsEmpty(ActiveCell) = False Then
‘ActiveCell has something in it.
If ActiveCell.Column = 8 Or ActiveCell.Column = 9 Then
‘ActiveCell is in Column H or I.
Range(“H6:I” & lngMaxRow).EntireRow.Hidden = True
End If
End If

HiliteMeAndSiblings ActiveCell

Application.ScreenUpdating = True

End Sub

Sub HiliteMeAndSiblings(oCell As Range)

Dim i As Long
‘oCell.Offset(0, -1).Resize(1, 2).Font.ColorIndex = 5
oCell.Offset(0, -1).EntireRow.Hidden = False
For i = 2 To lngMaxRow
Debug.Print i
If Range(“H” & i) = oCell & “*” Then
Range(“I” & i).EntireRow.Hidden = False
End If
Next i
HiliteMeAndSiblings2 ActiveCell

End Sub

Sub HiliteMeAndSiblings2(oCell As Range)

Dim i As Long
‘oCell.Offset(0, -1).Resize(1, 2).Font.ColorIndex = 5
oCell.Offset(0, 1).EntireRow.Hidden = False
For i = 2 To lngMaxRow
Debug.Print i
If Range(“I” & i) = oCell Then
Range(“H” & i).EntireRow.Hidden = False
End If
Next i

End Sub

Reply | Quote

Try

If Range(“H” & i) Like oCell & “*” Then

Reply | Quote

My thought had been to use:

    If Not Intersect(ActiveCell, Range("H:I")) Is Nothing And _
        ActiveCell  "" Then

But the way you did it (checking the column numbers) also works

Steve

Reply | Quote

ok one last word on this subject.

Is there any way That I can make it go faster because the wait is at least 4 minutes if there is more than 10 child/parents it has to find.?

And now that I have completed it how would you have written the code? Just curious!!

thank you

Reply | Quote

1) This has already been mentioned before. Instead of unhiding them all at once, use a range variable to hold all the rows to hide. Keep UNIONing the row that matches to the rows that already matched. then unhide this range all at once. That may speed it up.

Additionally, instead of looping thru the range in VB and comparing it row by row, use FIND to loop thru all the cells and mark them. This may be faster also.

You can test to see which one will have greater impact by comparing the hide/undhide scenario, versus a code which only finds and colors the appropriate cells. If the one that colors is much faster it is not a searching issue by an unhiding issue.

It may even be simpler codiing to mark the rows that should be visible with an “x” in a blank column as you go thru the code. the add the end just use autofilter to filter on the “X”.

2) My code is just some minor modifications of Hans’ code as I mentioned before: he highlighted, I hid. The major change unhiding all to start and checking for a blank cell or not in H/I to unhide all.

Option Explicit
Private lngMaxRow As Long
Sub EXEC_BTN_Click()
    Range("H4:H65536").EntireRow.Hidden = False
    lngMaxRow = Range("I65536").End(xlUp).Row
    If Not Intersect(ActiveCell, Range("H:I")) Is Nothing And _
        ActiveCell  "" Then
        Range("H4:I" & lngMaxRow).EntireRow.Hidden = True
        HiliteMeAndSiblings ActiveCell
    End If
End Sub
Sub HiliteMeAndSiblings(oCell As Range)
    Dim i As Long
    oCell.Offset(0, -1).EntireRow.Hidden = False
    For i = 2 To lngMaxRow
        If Range("H" & i) = oCell Then
            HiliteMeAndSiblings Range("I" & i)
        End If
    Next i
End Sub

Steve

Reply | Quote

As far as I can tell, the problem has now been resolved. Since this thread has become very long, I will lock it. If you have additional questions, please start a new thread, with a reference to this one if necessary.

Reply | Quote

I am still around.

The code unhides all the rows this is not slow. You should only be going to the lastrow of your data.

It must go thru each row in your range 1 row at a time to unhide the appropriate ones. This can be sped up 2 ways. Add:
Application.ScreenUpdating = False

at the front of the “Click:” code and add:
Application.ScreenUpdating = True
Ad the end of the click code. This will stop XL from redrawing the screen until the code is complete. this will help a little.

The 2nd way is, as mentioned earlier, not to unhide row by row. UNION all the ranges together first and then Unhide the UNIONed range. UNION is VB method and you can read about it in HELP.

Steve

Reply | Quote

Your “hiding all rows” should be in the “click routine” not the “HiliteMe” routine. You are unhiding the rows that match, then when you look for others you are hiding all again…

The “hide all” line replaces the “uncolor line” in the “click”. The click routine is also where you could check that the activecell had something in it and was within column H/I (HINT: read about the “Intersect” routine in VB Help. You could also search the postings here for code with intersect which tests for the activecell being in a range).

Steve

Reply | Quote

I am still confused at what you want/need.

If you do not want the code to run when you are on column B then do not run it, only run it when you are the column you want to check. you could, if desired, have the code check the column you are on and only run for certain columns.

A msgbox command can pop up messages (see VB help for more details).

If you want specific answers to specific questions you need to detail what you need help with.

Steve

Reply | Quote