#1
|
|||
|
|||
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. I’m 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. |
#2
|
||||
|
||||
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))) Is vba a must? |
#3
|
||||
|
||||
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))) |
#4
|
|||
|
|||
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 I’m not searching and C2 is empty all of the range cells are highlighted. I’m trying to add an IF statement that would prevent this.
|
#5
|
||||
|
||||
Include
Len($C$2)>0 as another part within the AND function. |
#6
|
|||
|
|||
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)))
|
#7
|
||||
|
||||
Code:
=and(Len($C$2)>0,isnumber(search($c$2,b4)),isnumber(match(row(b4),row(indirect($a$2 & ":" & $b$2)),0))) |
#8
|
|||
|
|||
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.
|
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 |
How to highlight cells using Vlookup / conditional formatting | mikehk | Excel | 2 | 09-18-2017 07:30 PM |
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 |