Change size of range depending on next Bold Cell

[colmguckian]

I have a code which defines Range1 as Range("A" & I, "A" & I + 15).Name = "Range1
Instead Of adding the 15 rows to the range, I would like the range
to stop one row before the next bold cell.
What would i need to do to the below code to achieve this

Dim I As Integer, DFuzzyMatchByWord As Double, C As Range
For I = 1 To 500
DFuzzyMatchByWord = FuzzyMatchByWord((ComboBox2.Value), Workbooks("Book1").Worksheets("Sheet1").Range("A" & I).Value)
If DFuzzyMatchByWord > 80 Then
Workbooks("Book1").Worksheets("Sheet1").Range("A" & I, "A" & I + 15).Name = "Range1"
End If
Next I

 

[Peter_SSs]

See if this inserted in your code does what you want, or gives you enough to go on. This is for Excel 2007+. If you have an earlier version then post back with that version.

<font face=Courier New><SPAN style="color:#00007F">Dim</SPAN> BoldFound <SPAN style="color:#00007F">As</SPAN> Range<br>    <br><SPAN style="color:#00007F">With</SPAN> Application.FindFormat<br>    .Clear<br>    .Font.FontStyle = "Bold"<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">With</SPAN> Workbooks("Book1").Worksheets("Sheet1")<br>    <SPAN style="color:#00007F">Set</SPAN> BoldFound = .Range("A" & I & ":A500").Find(What:="", _<br>        After:=.Cells(I, 1), LookAt:=xlPart, SearchDirection:=xlNext, _<br>        SearchFormat:=True)<br>    <SPAN style="color:#00007F">If</SPAN> BoldFound <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        MsgBox "No bold cells"<br>    <SPAN style="color:#00007F">Else</SPAN><br>        .Range("A" & I, BoldFound).Name = "Range1"<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN></FONT>

 

[colmguckian]

Thanks Peter.
I tried to change your code to do what I need
But I dont have the skills yet.