Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 06-17-2022, 12:07 AM
14spar15 14spar15 is offline Find and highlight cells within different ranges. Windows 7 64bit Find and highlight cells within different ranges. Office 2010 64bit
Advanced Beginner
Find and highlight cells within different ranges.
 
Join Date: Mar 2011
Posts: 79
14spar15 is on a distinguished road
Default Find and highlight cells within different ranges.

Hello, I would like to find the value in cell C2 in every case within the range of the value in cell A2 and value of cell B2. In the example here I would like cells B4, B6, and B9 to be highlighted or clearly pointed out somehow. When I run my Macro3 there are a few problems.



1. The results are shown in a message box. I just want the results highlighted.

2. Im only finding the first found result. I need to see all of them. I was going to try and figure out a loop but the range can sometimes be different.

3. In this example, B4 is not found because it does not start with gre.

Thanks for any help.
Attached Files
File Type: xlsm Book1.xlsm (14.9 KB, 6 views)
Reply With Quote
  #2  
Old 06-17-2022, 06:45 AM
p45cal's Avatar
p45cal p45cal is offline Find and highlight cells within different ranges. Windows 10 Find and highlight cells within different ranges. Office 2019
Expert
 
Join Date: Apr 2014
Posts: 634
p45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud of
Default

It's possible with conditional formatting (in cells B4:B30 in the attached):
Code:
=AND(ISNUMBER(SEARCH($C$2,B4)),ISNUMBER(MATCH(ROW(B4),ROW(INDIRECT($A$2 & ":" & $B$2)),0)))
The formula used is in column I but this is not needed - it was only for development of the CF formula.



Is vba a must?
Attached Files
File Type: xlsm msofficeforums49224Book1.xlsm (18.2 KB, 5 views)
Reply With Quote
  #3  
Old 06-17-2022, 07:35 AM
p45cal's Avatar
p45cal p45cal is offline Find and highlight cells within different ranges. Windows 10 Find and highlight cells within different ranges. Office 2019
Expert
 
Join Date: Apr 2014
Posts: 634
p45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud of
Default

Update, just realised that the range to looked at could be more than one column wide, so new CF formula:
Code:
=AND(ISNUMBER(SEARCH($C$2,B4)),ISNUMBER(MATCH(ROW(B4),ROW(INDIRECT($A$2 & ":" & $B$2)),0)),ISNUMBER(MATCH(COLUMN(B4),COLUMN(INDIRECT($A$2 & ":" & $B$2)),0)))
See attachment.
Attached Files
File Type: xlsm msofficeforums49224Book1.xlsm (18.6 KB, 5 views)
Reply With Quote
  #4  
Old 06-17-2022, 09:48 AM
14spar15 14spar15 is offline Find and highlight cells within different ranges. Windows 7 64bit Find and highlight cells within different ranges. Office 2010 64bit
Advanced Beginner
Find and highlight cells within different ranges.
 
Join Date: Mar 2011
Posts: 79
14spar15 is on a distinguished road
Default

That works great. Doing this with a formula is perfectly fine. I only need to search one column so I used your first formula. I was not familiar with array formulas. One last thing. If Im not searching and C2 is empty all of the range cells are highlighted. Im trying to add an IF statement that would prevent this.
Reply With Quote
  #5  
Old 06-17-2022, 10:22 AM
p45cal's Avatar
p45cal p45cal is offline Find and highlight cells within different ranges. Windows 10 Find and highlight cells within different ranges. Office 2019
Expert
 
Join Date: Apr 2014
Posts: 634
p45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud of
Default

Include
Len($C$2)>0
as another part within the AND function.
Reply With Quote
  #6  
Old 06-17-2022, 02:40 PM
14spar15 14spar15 is offline Find and highlight cells within different ranges. Windows 7 64bit Find and highlight cells within different ranges. Office 2010 64bit
Advanced Beginner
Find and highlight cells within different ranges.
 
Join Date: Mar 2011
Posts: 79
14spar15 is on a distinguished road
Default

I'm sorry, I have been trying for a hour and I can't figure out how to include this in the original formula. =AND(ISNUMBER(SEARCH($C$2,B4)),ISNUMBER(MATCH(ROW( B4),ROW(INDIRECT($A$2 & ":" & $B$2)),0)))
Reply With Quote
  #7  
Old 06-17-2022, 03:17 PM
p45cal's Avatar
p45cal p45cal is offline Find and highlight cells within different ranges. Windows 10 Find and highlight cells within different ranges. Office 2019
Expert
 
Join Date: Apr 2014
Posts: 634
p45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud of
Default

Code:
=and(Len($C$2)>0,isnumber(search($c$2,b4)),isnumber(match(row(b4),row(indirect($a$2 & ":" & $b$2)),0)))
Reply With Quote
  #8  
Old 06-17-2022, 10:04 PM
14spar15 14spar15 is offline Find and highlight cells within different ranges. Windows 7 64bit Find and highlight cells within different ranges. Office 2010 64bit
Advanced Beginner
Find and highlight cells within different ranges.
 
Join Date: Mar 2011
Posts: 79
14spar15 is on a distinguished road
Default

I needed to slow down and read this thing from the beginning. In your first message you instruct me that all we needed was the conditional formatting. I had added your latest addition for the empty "Find" correctly but I had forgotten to change the conditional format formula. I couldn't figure out why we needed the formula twice and that's when I remembered all we needed was the conditional formatting. Thank you so much for all your help and patience here. This is a real timesaver for me.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to highlight the cells using conditional formatting? LearnerExcel Excel 1 01-01-2019 01:43 AM
Copy & paste 2 ranges of cells trevorc Excel Programming 14 12-02-2018 03:08 PM
Find and highlight cells within different ranges. How to highlight cells using Vlookup / conditional formatting mikehk Excel 2 09-18-2017 07:30 PM
Find and highlight cells within different ranges. Take String of numbers, expand ranges, sort, then compress back into ranges AustinBrister Word VBA 19 08-22-2016 05:18 PM
find - reading highlight - highlight all / highlight doesn't stick when saved bobk544 Word 3 04-15-2009 03:31 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 07:43 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2022, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2022 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft