• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Search Range and highlight row when specific text is found

B

bnpdkh

Member
Hello again,
I have a work schedule sheet set up with a summary line at the top of the page. This line contains totals for priority codes, for example cell N3 has a formula to search and return the total number of times "DC" is found in range M9:M306. In this case there are 19 occurances. Is there a way to click on the cell (N3) that contains the total and then have the 19 different rows highlight or flash.
 
Hi,

Well flashing is what I will not suggest, but surely we can highlight cells which contains DC, when you click on N3 with the help of little VBA & CF.

See the file.

Regards,
 

Attachments

  • highligtCF.xlsm
    14.5 KB · Views: 13
I have looked at your example and i see how you have this set up. I am running into a problem on my worksheet and I think it is because I also have some vba run on worksheet_change. If I add your VBA it does not work, however if I disable the worksheet_change vba your code works perfectly. Any suggestions for getting around this problem?
 
Well I cannot comment on how that is happening because in my code there is just a line which assign Active cell address to a range. If you are putting that line inside an INTERSECT function than trying putting at the beginning of Worksheet_Selection Change Event.

Regards
 
Hi ,

It is a good practice to always enclose any statements which may affect any other event-triggered procedures within the following 2 statements :

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.EnableEvents = False
Range("S1") = Target.Address
Application.EnableEvents = True
End Sub

Narayan
 
Thank You!!! the example provided along with the "good practice" advise has done the trick. This advise has also cleared up some similar issues I have run into in other workbooks, absolutely awsome!!
One remaining issue if you have time, I want to add "CC" to the formatting formula provided but I keep getting formula error. I thought i would add OR to the AND statement below but it is not working. should be simple but I am missing something, any suggestions

Code: 
=AND("$N$3"=$IN$1,M9="DC")
 
Hi ,

If you are asking about the CF formula , I think Misra is the person to go to , since I have not gone through the CF formula to comment.

Narayan
 
@bnpdkh
Are you talking like either DC or CC in the cell should get highlighted?

If so than try below formula in CF
=AND("$N$3"=$IN$1,OR(M9="DC",M9="CC"))

You may also need to make the change in N3 formula also may be.

Regrads,
 
Thanks Misra, this works perfectly. Any ideas what I am doing wrong in the formula below. I keep getting a formula error

Code: 
=AND("$T$3"=$IN$1, ISNUMBER(SEARCH(HWD,H9)))
 
Please disregard previous post, when I seen it I knew what the issue was. I must have been looking at this too long, quotations were missing :mad: Thanks again for your help with original post it was very helpful.

Code: 
=AND("$T$3"=$IN$1, ISNUMBER(SEARCH("HWD",H9)))
 
You must log in or register to reply here.
Back
Top