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

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


Regards
Claus Busch

Smack on! Works perfect.

Thanks, Claus.

Howard

Smack on! Works perfect.

Thanks, Claus.

Howard
Hi Claus,

The code does produce the green triangle error notification in some cells, and I found this which cleans them up nicely.

Dim errC As Range, bError As Byte

For Each errC In OneRng.Cells
For bError = 1 To 7 Step 1
With errC
If .Errors(bError).Value Then
.Errors(bError).Ignore = True
End If
End With
Next bError
Next errC


Howard

Hi Howard,

Am Sun, 9 Aug 2015 04:17:51 -0700 (PDT) schrieb L. Howard:

The code does produce the green triangle error notification in some cells, and I found this which cleans them up nicely.

Dim errC As Range, bError As Byte

For Each errC In OneRng.Cells
For bError = 1 To 7 Step 1
With errC
If .Errors(bError).Value Then
.Errors(bError).Ignore = True
End If
End With
Next bError
Next errC

that's a way. I deactivated all errors into the options.


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

that's a way. I deactivated all errors into the options.


Regards
Claus Busch

Hi Claus,

I had formulas like this that counted the number of values shown in P1 in the named range Data1, which is one of nine blocks of nine cells within the larger range E2:M10.

There will always be only one P1 value per cell in each of the Data1, Data2, etc. named ranges cells.

So it really is a count of how many cells of the nine cells in the named range Data1 have the value of P1 in them.

This do not work with the numbers as text values now.

=SUMPRODUCT(1*NOT(ISERROR(FIND($P$1,Data1))))

This does not count the numbers as text either.

=COUNTIF(Data1,$P$1)

Any hints?

Howard

There will always be only one P1 value per cell in each of the Data1, Data2, etc. named ranges cells.

There will be only one P1 value OR none in each cell. So all nine cells of Data1 could have a P1 value or the least would be only one cell.

Hmmm, I'm thinking I still not explaining it properly.

Howard

Hi Howard,

Am Sun, 9 Aug 2015 06:11:36 -0700 (PDT) schrieb L. Howard:

There will always be only one P1 value per cell in each of the Data1, Data2, etc. named ranges cells.

So it really is a count of how many cells of the nine cells in the named range Data1 have the value of P1 in them.

try:

=COUNT(FIND(P1,Data1))
and insert the formula with CRTL+Shift+Enter


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

try:

=COUNT(FIND(P1,Data1))
and insert the formula with CRTL+Shift+Enter


Regards
Claus Busch


Good to go, and thanks for the good stuff.

Howard