habe eine Tabelle in der in den Zellen D3:F16 größere Texte stehen.
Über eine Formel, werden in meiner Spalte A1 einzelne Wörter aufgelistet.
Jetzt möchte ich diese Wörter im Bereich D3:F16 finden und hervorheben.
Hat jemand eine Idee?
Gruß Nathalie
Sub til()
Dim C As Range, D As Range
For Each D In Tabelle2.Range("D3:F19")
For Each C In Tabelle1.Range("A3:A5")
If D Like "*" & C & "*" Then
D.Characters(InStr(1, D, C), Len(C)).Font.Bold = True
End If
Next C
Next D
End Sub
VG, Boris
Sub til()
Dim C As Range, D As Range
Tabelle2.Range("D:F").Font.Bold = False
For Each D In Tabelle2.Range("D3:F19")
For Each C In Tabelle1.Range("A3:A5")
If D Like "*" & C & "*" Then
D.Characters(InStr(1, D, C), Len(C)).Font.Bold = True
End If
Next C
Next D
End Sub
VG, Boris
Option Explicit
Public Sub Worte_markieren()
Dim lngRow As Long, lngPosition As Long
Dim strText As String, strFirsAddress As String
Dim objCell As Range
With Worksheets("Tabelle1")
For lngRow = 3 To .Cells(.Rows.Count, 1).End(xlUp).Row
strText = .Cells(lngRow, 1).Text
Set objCell = .Columns("D:F").Find(What:=strText, _
LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False)
If Not objCell Is Nothing Then
strFirsAddress = objCell.Address
Do
lngPosition = InStr(1, objCell.Text, strText, vbTextCompare)
With objCell.Characters(lngPosition, Len(strText)).Font
.Color = vbRed
.Bold = True
End With
Set objCell = .Columns("D:F").FindNext(After:=objCell)
Loop Until objCell.Address = strFirsAddress
Set objCell = Nothing
End If
Next
End With
End Sub
Gruß
Option Explicit
Public Sub Worte_markieren()
Dim lngRow As Long, lngPosition As Long
Dim strText As String, strFirsAddress As String
Dim objCell As Range
With Worksheets("Tabelle1")
With .Range(.Cells(3, 4), .Cells(.Rows.Count, 6).End(xlUp)).Font
.Color = vbBlack
.Bold = False
End With
For lngRow = 3 To .Cells(.Rows.Count, 1).End(xlUp).Row
strText = .Cells(lngRow, 1).Text
Set objCell = .Columns("D:F").Find(What:=strText, _
LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False)
If Not objCell Is Nothing Then
strFirsAddress = objCell.Address
Do
lngPosition = InStr(1, objCell.Text, strText, vbTextCompare)
With objCell.Characters(lngPosition, Len(strText)).Font
.Color = vbRed
.Bold = True
End With
Set objCell = .Columns("D:F").FindNext(After:=objCell)
Loop Until objCell.Address = strFirsAddress
Set objCell = Nothing
End If
Next
End With
End Sub
Gruß
Option Explicit
Public Sub Worte_markieren()
Dim lngRow As Long, lngPosition As Long, lnglastRow As Long
Dim lngIndex As Long, lngPercent As Long
Dim strText As String, strFirsAddress As String
Dim objCell As Range
With Worksheets("Tabelle1")
With .Range(.Cells(3, 4), .Cells(.Rows.Count, 6).End(xlUp)).Font
.Color = vbBlack
.Bold = False
End With
lnglastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
lngPercent = CLng(100 / (lnglastRow - 2))
For lngRow = 3 To .Cells(.Rows.Count, 1).End(xlUp).Row
lngIndex = lngIndex + lngPercent
Application.StatusBar = " " & CStr(lngIndex) & " % " & String$(lngIndex \ 2, ChrW$(9609))
strText = .Cells(lngRow, 1).Text
Set objCell = .Columns("D:F").Find(What:=strText, _
LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False)
If Not objCell Is Nothing Then
strFirsAddress = objCell.Address
Do
lngPosition = InStr(1, objCell.Text, strText, vbTextCompare)
With objCell.Characters(lngPosition, Len(strText)).Font
.Color = vbRed
.Bold = True
End With
Set objCell = .Columns("D:F").FindNext(After:=objCell)
Loop Until objCell.Address = strFirsAddress
Set objCell = Nothing
End If
Next
End With
Application.StatusBar = False
End Sub
Gruß
Option Explicit
Public Sub Worte_markieren()
Dim lngRow As Long, lngPosition As Long, lnglastRow As Long
Dim lngIndex As Long, lngPercent As Long
Dim strText As String, strFirsAddress As String
Dim objCell As Range
With Worksheets("Tabelle1")
With .Range(.Cells(3, 4), .Cells(.Rows.Count, 6).End(xlUp)).Font
.Color = vbBlack
.Bold = False
End With
lnglastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
lngPercent = CLng(100 / (lnglastRow - 2))
For lngRow = 3 To .Cells(.Rows.Count, 1).End(xlUp).Row
lngIndex = lngIndex + lngPercent
Application.StatusBar = " " & CStr(lngIndex) & " % " & String$(lngIndex \ 2, ChrW$(9609))
If Not IsEmpty(.Cells(lngRow, 1).Value) Then
strText = .Cells(lngRow, 1).Text
Set objCell = .Columns("D:F").Find(What:=strText, _
LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False)
If Not objCell Is Nothing Then
strFirsAddress = objCell.Address
Do
lngPosition = InStr(1, objCell.Text, strText, vbTextCompare)
With objCell.Characters(lngPosition, Len(strText)).Font
.Color = vbRed
.Bold = True
End With
Set objCell = .Columns("D:F").FindNext(After:=objCell)
Loop Until objCell.Address = strFirsAddress
Set objCell = Nothing
End If
End If
Next
End With
Application.StatusBar = False
End Sub
Gruß