Find value and highlight

[zeuslorenzo]

Hi All

I need to search the value in sheet2 and look for the value in sheet1
if the value exist anywhere in the sheet then the value in the sheet2 should be highlighted.

I have code for this process however, it will only search for the values in a specific column not the entire sheet (used range). Please help me with the code. Also if there is a method to get this done via a formula please let me know.

Sub i77()
Dim Rng As Range
Dim loz As Range
Dim N As Integer
Dim cell As Range

Set Rng = Sheet2.UsedRange

N = 3

Application.ScreenUpdating = True

Do While Sheet2.Range("A" & N) <> ""

Set loz= Sheet1.Range("A" & N)

For Each cell In Rng

If cell.Value = loz Then cell.Interior.ColorIndex = 3

Next

N = N + 1

Loop
End Sub

Thanks in advance.

 

[Trouttrap2]

An alternative to VBA is conditional formatting (ALT, O, D). You could use that. If you have a dynamic range in Sheet2, then you should create a named range based on a dynamic formula.

 

[zeuslorenzo]

Thanks but conditional formatting can not be used within 2sheets. If there is a way please let me know. Also the comparision has to be between A" column in Sheet2 and entire sheet1.

 

[Trouttrap2]

Thats strange. I was able to make it work on separate sheets. Where the value is in Sheet1 and the conditional format is in Sheet2. The value in Sheet1 was highlighted in Sheet2.

Thanks but conditional formatting can not be used within 2sheets. If there is a way please let me know. Also the comparision has to be between A" column in Sheet2 and entire sheet1.

 

[MrJasonLe]

Hi zeuslorenzo

Were you able to solve your query? Can you share with us how you went with your conditional formatting (CF)?

I have a similar scenario and would be interested very much on your success (hopefully?) and how you achieved it in the end.

My scenario:

Sheet 1, column A only:
Row 1: GAA
Row 2: GAX
Row 3: HRA
Row 4: HRB
Row 5: LAB
Row 6: LAX
Plus many more rows, each one containing only a three-character code, similar to the sample ones shown above.

Sheet 2, column A only:
Row 1: GAA
Row 2: GAA+TLA
Row 3: GAX,GAB
Row 4: GAX+HRA,GAX+HRX
Row 5: HRA,HRB
Row 6: HRA+LAA,HRA+LAB
Row 7: GAA+HRA+LAA,GAA+HRA+LAB,GAB+HRA+LAA
Row 8: HRA+LAC,HRB+LAC,HRB+LAW,HRX+LAW
Plus many more rows, each one containing any combination of three-character codes, similar to the sample ones shown above, i.e. some rows/cells have only one code, while others have two or more codes, joined together by the plus (+) sign or separated by the comma (,). Five or six codes is the highest number of codes that are joined by the plus (+) sign (let's call this a 'combination'); but a cell may have five, six or more of these 'combinations', therefore, potentially giving a cell a total of 30 or 40 or more codes. Where a cell has more than one code, between the codes are no spaces but just either a plus sign (+) or a comma (,). (And there are no preceding or trailing spaces, either.)

Conditional formatting required:

If any of the codes in Sheet 1 column A appears in Sheet 2 column A, then format/change the font color of that code as it appears in Sheet 2 column A to , say, red. Note that the red color is to apply to the text only and not the whole cell.

So in the above sample data, in Sheet 2 column A, the following codes will be formatted to red:

Row 1: GAA
Row 2: GAA+TLA
Row 3: GAX,GAB
Row 4: GAX+HRA,GAX+HRX
Row 5: HRA,HRB
Row 6: HRA+LAA,HRA+LAB
Row 7: GAA+HRA+LAA,GAA+HRA+LAB,GAB+HRA+LAA
Row 8: HRA+LAC,HRB+LAC,HRB+LAW,HRX+LAW

What I have tried so far:

For those cells in Sheet 2 column A with up to four codes in total, I can bring each code into four new column, by functions =left(), =mid() or right(), and then do a vlookup for each new column and then filter the vlookup results and manually format/change the font color depending what's returned in the vlookup. But this process is impractical for those cells (and there are many) that have 5, 10, 20 or more codes.

Let's call the above conditional formatting steps Step 1.

Step 2 is a further check, as follows:

- If a cell contains only one code and that code is red, then that cell is TRUE
- If a cell contains more than one code but each code is separate by a comma (,) and any of these codes is red, then that cell is TRUE
- If a cell contains a combination of codes or more than one combination of codes (i.e. codes joined together by plus sign (+)), and all the codes in any one combination are red, then that cell is TRUE

So in the above sample data, the results of Step 2 would be:

Row 1: GAA = TRUE
Row 2: GAA+TLA = FALSE
Row 3: GAX,GAB = TRUE
Row 4: GAX+HRA,GAX+HRX = TRUE
Row 5: HRA,HRB = TRUE
Row 6: HRA+LAA,HRA+LAB = TRUE
Row 7: GAA+HRA+LAA,GAA+HRA+LAB,GAB+HRA+LAA = TRUE
Row 8: HRA+LAC,HRB+LAC,HRB+LAW,HRX+LAW = FALSE

I am more advanced than a novice but probably low mid-level. I can do basic conditional formatting or work my way through the instructions for some basic CF but the Step 1 above is beyond my league.

I started out this post seeking assistance with the Step 1 outlined above, but I thought to also include Step 2 to give the whole picture. If this CF for Step 1 is within easy grasp of any fellow forum members, I would be very grateful if you would assist with the exact formula or steps, or any relevant ideas to help achieve same. If you can also assist with the Step 2, then I'll be very very much obliged and my hat would be off to you good sirs and/or madams!

Thank you and here's hoping...

-Jason, using Excel 2010