Change font size of rows in a range with bold letters

[jazz2409]

HI, is there a way to Change the font size of rows in a range with bold letters?

I was trying to do it using conditional formatting but it won't work

Thank you

[Tom Jones]

Only with VBA code

[jazz2409]

Only with VBA code

Yes, how?

For example the value of cell B8 is on Column W of Sheet 2, range A8:08 will be bold letters

[Tom Jones]

Yes, how?

For example the value of cell B8 is on Column W of Sheet 2, range A8:08 will be bold letters

I don't understand. Upload a file, and explain exactly what you need.

In first post you say that you want to change the font size of bold font. Is that OK?
If you change font size the row height will grow.

[jazz2409]

Sorry I got confused with everything I am searching about.

What I am trying to do is change the font size of the entire range to 10 if a value on column B is on the column W of Sheet 2.

I tried conditional formatting however it doesn't allow changing of font size

[Paul_Hossler]

What I am trying to do is change the font size of the entire range to 10 if a value on column B is on the column W of Sheet 2.




Option Explicit


Sub ChangeFontSize()
Dim r1 As Range, r2 As Range
Dim iRow As Long, n As Long

Set r1 = Worksheets("Sheet1").Range("B7").CurrentRegion
Set r2 = Worksheets("Sheet2").Columns(23).SpecialCells(xlCellTypeConstants, xlTextValues)

For iRow = 2 To r1.Rows.Count
n = 0
On Error GoTo 0
n = Application.WorksheetFunction.Match(r1.Cells(iRow, 1), r2, 0)
On Error GoTo 0

If n > 0 Then
r1.Font.Size = 10
Exit For
End If
Next iRow


End Sub

[paulked]

Or for the single line



Sub RaiseFont()
Dim i As Long, j As Long, lr1 As Long, lr2 As Long
lr1 = Sheet2.Cells(Rows.Count, 23).End(xlUp).Row
lr2 = Sheet1.Cells(Rows.Count, 2).End(xlUp).Row
Sheet1.Cells(7, 2).CurrentRegion.Font.Size = 8
For i = 8 To lr2
For j = 2 To lr1
If Sheet2.Cells(j, 23) = Sheet1.Cells(i, 2) Then
Sheet1.Range("B" & i & ":O" & i).Font.Size = 10
End If
Next
Next
End Sub

[jazz2409]

Option Explicit


Sub ChangeFontSize()
Dim r1 As Range, r2 As Range
Dim iRow As Long, n As Long

Set r1 = Worksheets("Sheet1").Range("B7").CurrentRegion
Set r2 = Worksheets("Sheet2").Columns(23).SpecialCells(xlCellTypeConstants, xlTextValues)

For iRow = 2 To r1.Rows.Count
n = 0
On Error GoTo 0
n = Application.WorksheetFunction.Match(r1.Cells(iRow, 1), r2, 0)
On Error GoTo 0

If n > 0 Then
r1.Font.Size = 10
Exit For
End If
Next iRow


End Sub



Hi Paul, I am getting an error saying "unable to get the match property of the worksheetfunction class". I tried to change it to Application.Match like what it says here https://stackoverflow.com/questions/17751443/excel-vba-cant-get-a-match-error-unable-to-get-the-match-property-of-the-wor but it says type mismatch

[jazz2409]

Or for the single line



Sub RaiseFont()
Dim i As Long, j As Long, lr1 As Long, lr2 As Long
lr1 = Sheet2.Cells(Rows.Count, 23).End(xlUp).Row
lr2 = Sheet1.Cells(Rows.Count, 2).End(xlUp).Row
Sheet1.Cells(7, 2).CurrentRegion.Font.Size = 8
For i = 8 To lr2
For j = 2 To lr1
If Sheet2.Cells(j, 23) = Sheet1.Cells(i, 2) Then
Sheet1.Range("B" & i & ":O" & i).Font.Size = 10
End If
Next
Next
End Sub


Hi Paul, this works on the file I attached but not on my original file.. Let me try again

Update: it's working. I just forgot to change the names of the sheets :facepalm:

thanks to both of you. :)

[Paul_Hossler]

Hi Paul, I am getting an error saying "unable to get the match property of the worksheetfunction class". I tried to change it to Application.Match like what it says here https://stackoverflow.com/questions/17751443/excel-vba-cant-get-a-match-error-unable-to-get-the-match-property-of-the-wor but it says type mismatch

Glad the other Paul's worked for you, but I don't get a Type Mismatch error

I went back to see if I made a mistake, but it looks OK to me :dunno

BTW, I took 'entire range' to be the block of cells around B7.

[jazz2409]

Glad the other Paul's worked for you, but I don't get a Type Mismatch error

I went back to see if I made a mistake, but it looks OK to me :dunno

BTW, I took 'entire range' to be the block of cells around B7.


It actually does work on the file I attached, but not on my original file :(
What does that error mean anyway?

[Paul_Hossler]

Without seeing the details of the line and it's context that threw the error, this is only a guess

Usually means that you tried to assign something to an incompatible variable, e.g.




i = 100 + "A"

Set Range = "A" instead of Range.Value = "A"

[jazz2409]

Without seeing the details of the line and it's context that threw the error, this is only a guess

Usually means that you tried to assign something to an incompatible variable, e.g.




i = 100 + "A"

Set Range = "A" instead of Range.Value = "A"




what if I want to change the color as well if the values on column C:O are less than 100? say to color red?

[paulked]

Sub RaiseFont()
Dim i As Long, j As Long, k As Long, lr1 As Long, lr2 As Long, sh1 As Worksheet, sh2 As Worksheet
Set sh1 = ThisWorkbook.Sheets("Sheet1")
Set sh2 = ThisWorkbook.Sheets("Sheet2")
lr1 = sh2.Cells(Rows.Count, 23).End(xlUp).Row
lr2 = sh1.Cells(Rows.Count, 2).End(xlUp).Row
With sh1.Cells(7, 2).CurrentRegion.Font
.Size = 8
.Color = vbBlack
.Bold = False
End With
For i = 8 To lr2
For j = 2 To lr1
If sh2.Cells(j, 23) = sh1.Cells(i, 2) Then
sh1.Range("B" & i & ":O" & i).Font.Size = 10
For k = 3 To 15
If sh1.Cells(i, k) < 100 Then
With sh1.Cells(i, k).Font
.Color = vbRed
.Bold = True
End With
End If
Next
End If
Next
Next
End Sub


Only one place to change your sheet names this time!

[jazz2409]

Sub RaiseFont()
Dim i As Long, j As Long, k As Long, lr1 As Long, lr2 As Long, sh1 As Worksheet, sh2 As Worksheet
Set sh1 = ThisWorkbook.Sheets("Sheet1")
Set sh2 = ThisWorkbook.Sheets("Sheet2")
lr1 = sh2.Cells(Rows.Count, 23).End(xlUp).Row
lr2 = sh1.Cells(Rows.Count, 2).End(xlUp).Row
With sh1.Cells(7, 2).CurrentRegion.Font
.Size = 8
.Color = vbBlack
.Bold = False
End With
For i = 8 To lr2
For j = 2 To lr1
If sh2.Cells(j, 23) = sh1.Cells(i, 2) Then
sh1.Range("B" & i & ":O" & i).Font.Size = 10
For k = 3 To 15
If sh1.Cells(i, k) < 100 Then
With sh1.Cells(i, k).Font
.Color = vbRed
.Bold = True
End With
End If
Next
End If
Next
Next
End Sub


Only one place to change your sheet names this time!

Hi Paul, this works perfectly (as expected)! Thank you! One question, though. I don't get this part:


For k = 3 To 15 why is it 3 to 15?

[Tom Jones]

why is it 3 to 15?

Columns 3 to 15 (column "C" to "O")

[paulked]

Thank you Tom, you're welcome Jazz :thumb

[jazz2409]

Thanks, guys. :)

[Paul_Hossler]

what if I want to change the color as well if the values on column C:O are less than 100? say to color red?

Assuming that you only want to do the rows where you find a match, you could use conditional formatting



Option Explicit


Sub ChangeFontSize()
Dim r1 As Range, r2 As Range
Dim iRow As Long, n As Long

Set r1 = Worksheets("Sheet1").Range("B7").CurrentRegion
Set r2 = Worksheets("Sheet2").Columns(23).SpecialCells(xlCellTypeConstants, xlTextValues)

r1.FormatConditions.Delete

For iRow = 2 To r1.Rows.Count
n = 0
On Error Resume Next
n = Application.WorksheetFunction.Match(r1.Cells(iRow, 1), r2.Columns(1), 0)
On Error GoTo 0

If n > 0 Then
With r1.Rows(iRow)
.Font.Size = 10
.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, Formula1:="=100"
.FormatConditions(.FormatConditions.Count).SetFirstPriority
.FormatConditions(.FormatConditions.Count).Font.Bold = True
.FormatConditions(.FormatConditions.Count).Font.Color = vbRed
End With
End If
Next iRow


End Sub


PS - found my bug in #8 (dumb Copy/Paste error)

[jazz2409]

I will try both and see which one does what it's supposed to do faster �� Thank you ��

[jazz2409]

I will try both and see which one does what it's supposed to do faster 😊 Thank you 😊