View Full Version : [SOLVED:] Change font size of rows in a range with bold letters
jazz2409
04-27-2020, 07:47 AM
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
04-27-2020, 08:21 AM
Only with VBA code
jazz2409
04-27-2020, 08:37 AM
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
04-27-2020, 09:22 AM
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
04-27-2020, 09:51 AM
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
04-27-2020, 11:20 AM
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
04-27-2020, 11:49 AM
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
04-27-2020, 05:58 PM
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
04-27-2020, 06:03 PM
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
04-27-2020, 06:30 PM
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
04-27-2020, 06:59 PM
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
04-27-2020, 07:59 PM
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
04-27-2020, 08:17 PM
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
04-27-2020, 09:07 PM
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
04-27-2020, 10:23 PM
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
04-27-2020, 10:48 PM
why is it 3 to 15?
Columns 3 to 15 (column "C" to "O")
paulked
04-27-2020, 11:19 PM
Thank you Tom, you're welcome Jazz :thumb
jazz2409
04-27-2020, 11:43 PM
Thanks, guys. :)
Paul_Hossler
04-28-2020, 04:29 AM
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
04-28-2020, 08:56 PM
I will try both and see which one does what it's supposed to do faster Thank you
jazz2409
04-28-2020, 08:57 PM
I will try both and see which one does what it's supposed to do faster 😊 Thank you 😊
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.