Type mismatch (Error 13)

[Xcuse]

Hey,

I'm trying to get the value (text) from one cell (works) and then find all other matching cells in the document and highlight them with color.

The first part until Next MyCell works perfectly, my problem is that this code wouldn't find the word f.e. "After" if I'd search for "Aft" or "after".

So I tried to do another one (from Dim ws As Worksheet until end) but somehow I cant get it to work.

"Search_For_Word" stands for cell J4 and I renamed that one so its fixed.

Im using Excel 2016, Windows 11 x64

I hope someone has ideas!

Sub Color_Cell_From_Value_Of_Cell()

Dim CellValue As String

CellValue = Range("Search_For_Word").Value



Dim MyCell As Range
Dim StatValue As String
Dim StatusRange As Range

Set StatusRange = Range("Status")

For Each MyCell In StatusRange

StatValue = MyCell.Value


Select Case StatValue

    Case CellValue
    MyCell.Interior.Color = RGB(255, 255, 0)

End Select

Next MyCell

Dim ws As Worksheet
Set ws = Worksheets("Tabelle1")

    If ws.Range("Search_For_Word") = Application.WorksheetFunction.Search(CellValue, ws.Range("A1:G119")) Then
        
        Range("Status").Interior.Color = RGB(255, 255, 0)
        
    End If
   

End Sub

 

[Joe4]

Welcome to the Board!

I don't think you can use the SEARCH function for this.
The SEARCH function is not meant to search over a range of cells.
It is meant to locate a substring within a single string.
See: MS Excel: How to use the SEARCH Function (WS)

I think you want to use the "Range.Find" method instead.
See: Excel VBA Find - How to find any value in a range of cells with VBA - VBA and VB.Net Tutorials, Education and Programming Services

 

[Xcuse]

Welcome to the Board!

I don't think you can use the SEARCH function for this.
The SEARCH function is not meant to search over a range of cells.
It is meant to locate a substring within a single string.
See: MS Excel: How to use the SEARCH Function (WS)

I think you want to use the "Range.Find" method instead.
See: Excel VBA Find - How to find any value in a range of cells with VBA - VBA and VB.Net Tutorials, Education and Programming Services

Thanks for your quick reply, but sadly it doesn't really help me. I dont really know how to apply the .Find in my code and problem.
I still have no clue how I can locate the found Cells and color them.

That's what I tried so far:

Dim foundRng As Range

Set foundRng = StatusRange.Find("CellValue")

If foundRng Is Nothing Then

Else
    foundRng.Address.Interior.Color = RGB(255, 255, 0)

 

[Joe4]

I dont really know how to apply the .Find in my code and problem.

Did you look at the link I included, which shows you what this looks like?

 

[Xcuse]

Did you look at the link I included, which shows you what this looks like?

Yes I did but I don't really get how I can use it for my thing. Because there are only message boxes used. And I still don't know how to do it properly.

 

[Joe4]

It might be better to loop through the range and check each value.

So maybe replace this section:

    If ws.Range("Search_For_Word") = Application.WorksheetFunction.Search(CellValue, ws.Range("A1:G119")) Then
        
        Range("Status").Interior.Color = RGB(255, 255, 0)
        
    End If

With something like this:

Dim cell as Range
For Each cell in ws.Range("A1:G119")
    If cell.Value = CellValue Then
        cell.Interior.Color = RGB(255, 255, 0)
    End If
Next cell

 

[Xcuse]

This code works actually, but has the same problem as the code I already had: The code wouldn't find the word f.e. "After" if I'd search for "Aft" or "after".

 

[Joe4]

OK, so you it appears you want partial searches that are not case sensitive.
Try this:

Dim cell as Range
For Each cell in ws.Range("A1:G119")
    If InStr(LCase(cell.Value), LCase(CellValue)) > 0 Then
        cell.Interior.Color = RGB(255, 255, 0)
    End If
Next cell

 

[Xcuse]

OK, so you it appears you want partial searches that are not case sensitive.
Try this:

Dim cell as Range
For Each cell in ws.Range("A1:G119")
    If InStr(LCase(cell.Value), LCase(CellValue)) > 0 Then
        cell.Interior.Color = RGB(255, 255, 0)
    End If
Next cell

Thank you very much! That's exactly the solution I was looking for

 

[Joe4]

You are welcome.
Glad I was able to help!