OneRng values are numbers 1 to 9, such as 123, 124567, 3, 123456789, 456.

If Range("P1") = 3 then I want the 3 in each cell that has a 3, for the 3 to be BOLD and font size to 14.

My change code MsgBox correctly returns the POSITION of the P1 value in each cell of OneRng.

I can't figure how to use that to do a FONT & BOLD for that position.

Thanks.
Howard

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count 1 Then Exit Sub
If Target < Range("P1") Then Exit Sub

Dim OneRng As Range, c As Range
Dim pInt As Long, pPos As Long

pInt = Range("P1")
Set OneRng = Range("E2:M10")

For Each c In OneRng
If InStr(c, pInt) 0 Then
pPos = InStr(c, pInt)

MsgBox pPos

Else
End If
Next

End Sub

Hi Howard,

Am Sat, 8 Aug 2015 23:53:07 -0700 (PDT) schrieb L. Howard:

OneRng values are numbers 1 to 9, such as 123, 124567, 3, 123456789, 456.

If Range("P1") = 3 then I want the 3 in each cell that has a 3, for the 3 to be BOLD and font size to 14.

to format a substring is only possible with TEXT, not with NUMBERS.
If you don't want to calculate with the values you can change it to
text.
Try:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("E2:M10")) Is Nothing _
Or Target.Count 1 Then Exit Sub

Dim pInt As String

Range("E2:M10").NumberFormat = "@"
pInt = Range("P1")

With Target
With .Characters(InStr(.Text, pInt), 1)
.Font.Bold = True
.Font.Size = 14
End With
End With
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

to format a substring is only possible with TEXT, not with NUMBERS.
If you don't want to calculate with the values you can change it to
text.
Try:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("E2:M10")) Is Nothing _
Or Target.Count 1 Then Exit Sub

Dim pInt As String

Range("E2:M10").NumberFormat = "@"
pInt = Range("P1")

With Target
With .Characters(InStr(.Text, pInt), 1)
.Font.Bold = True
.Font.Size = 14
End With
End With
End Sub


Regards
Claus B.

Hi Claus,

Aha, more complicated than I thought.

I should have included in my first post that cell P1 is a drop down with the 1 to 9 numbers, and when I select a number in P1 it is a single digit from 1 to 9, then all the same numbers as P1 in the E2:M10 range are bold and font size 14.

So the Target is range P1.

The numbers are NOT for calculating either in P1 or the E2:M10 range.

Howard

I'm trying to reassemble your code but not making much progress.

Howard

Hi Howard,

Am Sun, 9 Aug 2015 01:59:58 -0700 (PDT) schrieb L. Howard:

I should have included in my first post that cell P1 is a drop down with the 1 to 9 numbers, and when I select a number in P1 it is a single digit from 1 to 9, then all the same numbers as P1 in the E2:M10 range are bold and font size 14.

So the Target is range P1.

then try:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(0, 0) < "P1" Or Target.Count 1 Then Exit Sub

Dim OneRng As Range, rngC As Range
Dim PInt As String, pos As Long

Set OneRng = Range("E2:M10")
OneRng.Font.Bold = False
PInt = Range("P1")

For Each rngC In OneRng
pos = InStr(CStr(rngC.Value), PInt)
With rngC
If pos 0 Then
.NumberFormat = "@"
With .Characters(pos, 1)
.Font.Bold = True
.Font.Size = 14
End With
End If
End With
Next

End Sub


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

I should have included in my first post that cell P1 is a drop down with the 1 to 9 numbers, and when I select a number in P1 it is a single digit from 1 to 9, then all the same numbers as P1 in the E2:M10 range are bold and font size 14.

So the Target is range P1.

Hi Claus,

I think I am still misleading you with bad directions.

Here is a sample column of nine the columns in the OneRng.

1367
167
4
139
1379
1489
2
135
8


If P1 = 4 then the single 4 and the 4 in 1489 should both be bold and font 14.

It is only affecting the lone digit 4 as written.

Howard

Hi Howard,

Am Sun, 9 Aug 2015 03:08:02 -0700 (PDT) schrieb L. Howard:

Here is a sample column of nine the columns in the OneRng.

1367
167
4
139
1379
1489
2
135
8

If P1 = 4 then the single 4 and the 4 in 1489 should both be bold and font 14.


look he
https://onedrive.live.com/redir?resi...121822A3%21326
for "Format_Substrings"


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2