Hi Raj,

Have a go with this -

Sub test2()
Dim rng As Range
Dim cell As Range

SampleData

On Error GoTo errExit

Set rng = Range("A1:A400")

Application.ScreenUpdating = False

rng.Interior.ColorIndex = xlNone
rng.Font.Bold = False

For Each cell In rng
FormatWordCaps cell
Next

errExit:
Application.ScreenUpdating = True
End Sub

Sub SampleData()
[a1] = "The TKRTC value in the 765TW field is not the default."
[a2] = "His code is CND8599, and pin is 2588."
[a3] = ""
[a4] = "No words in this line with all caps"

Range("A1:A4").Copy Range("A1:A400")
Range("A1:A4").Columns.AutoFit

End Sub

Function FormatWordCaps(cell As Range) As Long
Dim b As Boolean
Dim i As Long, j As Long, k As Long, n As Long
Dim s As String
Dim v
Dim ba() As Byte
Dim Words() As String

If Left$(cell.Formula, 1) = "=" Then Exit Function

s = cell.Value
If Len(s) = 0 Then Exit Function

Do
s = Replace(s, " ", " ")
n = InStr(n + 1, s, " ")
Loop Until n = 0

If Len(s) < Len(cell) Then cell.Value = s

Words = Split(Replace(Replace(s, vbLf, " "), vbCr, " "))

ReDim pos(1 To Len(s))

n = 1
For i = 0 To UBound(Words)
If Words(i) = UCase(Words(i)) And Words(i) Like "[A-Z0-9]*" Then
ba = Words(i)
k = 0
For j = 0 To UBound(ba) Step 2
k = k + 1
If ba(j + 1) 0 Then ba(j) = 0
Select Case ba(j)
Case 48 To 57, 65 To 90
'0 to 9, A to Z
Case Else ' probably punctuation
k = k - 1
Exit For
End Select
Next
pos(n) = k
End If
n = n + Len(Words(i)) + 1
Next

k = 0
For i = 1 To UBound(pos)
If pos(i) Then
k = k + 1
cell.Characters(i, pos(i)).Font.Bold = True
End If
Next

If k Then cell.Interior.ColorIndex = 36
FormatWordCaps = k
End Function

Regards,
Peter T

"Raj" wrote in message
...
In an earlier post:

http://groups.google.co.in/group/mic...ae9e72ad?hl=en
I had sought help in extracting words containing capital letters and/
or numbers from cells in Column C to corresponding cells in Column D.
eg. Column C2 has the sentence "The TKRTC value in the 765TW field is
not the default.". I wanted Column D2 to have "TKRTC 765TW".
Ron Rosenfeld and Rick Rothstein had contributed with Regexp and non-
Regexp solutions on that occasion.
Now, instead of Column D, I would like cells in Column C containing
such words be filled with a certain color and the word(s) be
displayed in bold.
I would love to see both Regexp and non-Regexp versions of the vba
code for this.

Thanks in advance for all the help.
Raj