IMPORTANT!Conditionally formatting just 2 of many characters in a

[Kay]

Hello,

Of course, all are aware that conditional formats are specific to a cell
using the format option in Excel. Can code be written that will search for
the characters " eg" with a space before and format only those characters?
The client is adamant and it takes forever to search for the characters and
then format manually.

Hope someone will help!

Thanks

 

[Gary Keramidas]

maybe something like will work. i used column A on sheet1 in this example

Option Explicit

Sub test()
Dim i As Long
Dim lastrow As Long
Dim startChar As Long
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row

For i = 1 To lastrow
startChar = InStr(1, ws.Range("A" & i).Value, " eg", vbTextCompare)
If startChar > 1 Then
With ws.Range("A" & i)
.Characters(startChar + 1, 2).Font.Bold = True
End With
End If
Next

End Sub

 

[OssieMac]

Hi Kay,

The following macro searches the entire used area of the worksheet for the
character string and formats only the specific characters being searched.

After the formatting line:-
..Color = vbRed

you can add other formatting like:-
..Bold = True

Sub Format_Text()
Dim strToFind As String
Dim lngTofind As Long
Dim rngUsed As Range
Dim foundCell As Range
Dim startPos As Long
Dim firstAddress As String

strToFind = " eg" 'Set to required string

lngTofind = Len(strToFind)

With Sheets("Sheet1") 'Edit for your sheet name
Set rngUsed = .UsedRange
End With

With rngUsed
Set foundCell = .Find(What:=strToFind, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

If Not foundCell Is Nothing Then
firstAddress = foundCell.Address

Do
startPos = InStr(1, foundCell, strToFind)

With foundCell.Characters(Start:=startPos, _
Length:=lngTofind).Font
.Color = vbRed
End With
Set foundCell = .FindNext(foundCell)
Loop While Not foundCell Is Nothing And _
foundCell.Address <> firstAddress
End If
End With
End Sub

 

[Kay]

Thanks to both who responded. I was actually able to get the test macro to
work, but the second macro seemed to find only the first t wo characters of a
string. But, since I am very weak in code, I probably did somsething wrong

Is the coding the same for access?

 

[OssieMac]

Hi Kay,

Do I interpret your comment to mean that " eg" can appear more than once in
the cells? If so, then the following will fix it although you probably do not
need it now if Gary's macro did the job.

In answer to your question "Is the coding the same for access?" I would say
that the answer is No. However, the principles of VBA remain the same and
there are a lot of similarities between them but for every similarity there
in another unique in each application.

Sub Format_Text()
Dim strToFind As String
Dim lngTofind As Long
Dim rngUsed As Range
Dim foundCell As Range
Dim startPos As Long
Dim firstAddress As String
Dim i As Long

strToFind = " eg" 'Set to required string

lngTofind = Len(strToFind)

With Sheets("Sheet1") 'Edit for your sheet name
Set rngUsed = .UsedRange
End With

With rngUsed
Set foundCell = .Find(What:=strToFind, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

If Not foundCell Is Nothing Then
firstAddress = foundCell.Address

Do
For i = 1 To Len(foundCell)
startPos = InStr(i, foundCell, strToFind)
If startPos > 0 Then
With foundCell.Characters(Start:=startPos, _
Length:=lngTofind).Font
.Color = vbRed
'.Bold = True 'Other formatting if required
End With
End If
Next i
Set foundCell = .FindNext(foundCell)
Loop While Not foundCell Is Nothing And _
foundCell.Address <> firstAddress
End If
End With
End Sub

 

[Kay]

Ossie Mac,

Thanks so much for you help. I like the loop because it is possible that
the eg will occur more than once in a cell.

You guys are the greatest!

Kay