CODEWITHSUNDEEP
vbafor-loopforeachfonts
Tarik
Tarik

Reputation: 77

changing font format for each sheet

I am kind of stuck with changing a font size for each sheet. I also want to change the font into bold when a value in the "E" column is below 18.

Dim ws As Worksheet

    ' Loop through all of the worksheets in the active workbook.
    For Each ws In ActiveWorkbook.Worksheets

        ' format font of currently looped worksheet object feferenced by WS
        With ws.Cells.Font
            .Name = "Calibri"
            .Size = 9
            .Strikethrough = False
            .Superscript = False
            .Subscript = False
            .OutlineFont = False
            .Shadow = False
            .Underline = xlUnderlineStyleNone
            .TintAndShade = 0
            .ThemeFont = xlThemeFontNone


            cr = ws.Cells(.Rows.Count, "A").End(xlUp).Row

            If Cells(cr, 5).Value < 18 Then
                Selection.Font.Bold = True
            End If

        End With

    Next ws

Any tips for me to fix the problem.

I am getting an error 438 on this line:

    cr = .Cells(.Rows.Count, "A").End(xlUp).Row

I also tried 

    cr = ws.Cells(.Rows.Count, "A").End(xlUp).Row

Both codes are not working...Help

Upvotes: 0

Views: 1089

Answers (2)

Tarik
Tarik

Reputation: 77

I fixed the answer:

' Declare Current as a worksheet object variable.
Dim ws As Worksheet

    ' Loop through all of the worksheets in the active workbook.
    For Each ws In ActiveWorkbook.Worksheets

        ' format font of currently looped worksheet object feferenced by WS
        With ws
            .Cells.Font.Name = "Calibri"
            .Cells.Font.Size = 9
            .Cells.Font.Strikethrough = False
            .Cells.Font.Superscript = False
            .Cells.Font.Subscript = False
            .Cells.Font.OutlineFont = False
            .Cells.Font.Shadow = False
            .Cells.Font.Underline = xlUnderlineStyleNone
            .Cells.Font.TintAndShade = 0
            .Cells.Font.ThemeFont = xlThemeFontNone

            cr = .Cells(.Rows.Count, "A").End(xlUp).Row
            For i = 1 To cr 'loop through all value in column e
                   If .Cells(i, 5).Value < 18 Then .Cells(i, 5).Font.Bold = True
            Next i

        End With

    Next ws

Upvotes: 1

Doug
Doug

Reputation: 265

What error have you got? I think the code above would change the font size just fine. BUT the code changing font for cells with value under 18 to bold doesn't work.

You want to change each cell to bold, if the rule applied, right? Try "conditional formating" (will apply each time cell value changed and < 18) or modify your code like this (will apply each time you run the macro):

...
Dim i as integer
cr = ws.Cells(.Rows.Count, "A").End(xlUp).Row
For i = 1 to cr 'loop through all value in column e
       If .cells(i,5).value <18 then .cells(i,5).font.bold = true
Next i
....

Upvotes: 1

Related Questions