VBA (?) How to increase text size for an entire row, based on 1 cell's content - conditional formatting

[bmcley]

Hello, and thanks in advance for the help.

Using Excel 2010 on a Windows 7 machine.

I have a list of about 4000 items (products). I want my salespeople to be able to run through the list and quickly identify which items are being purchased by a customer.

I have already created the formula to show whether an item is an "A","B","C", or "D" item. This is populated by a VLOOKUP statement in column G.

Essentially I want to shrink the text very small (say 8) to maximize page space, but if column G shows an A, B, or C, I want that entire row to jump to a larger size of text (say 16). Arial or Calibri is fine.

I have them conditionally formatted to highlight, but that not really doing what I need.

Again, thanks in advance for your help.

 

[lrobbo314]

How about this?

Option Explicit
Option Compare Text


Sub fSize()
Dim r   As Range
Dim cel As Range

Set r = Range("G1", Range("G" & Rows.Count).End(xlUp))

    For Each cel In r
        With cel
            Select Case cel
                Case "A"
                    .Font.Size = 16
                Case "B"
                    .Font.Size = 16
                Case "C"
                    .Font.Size = 16
                Case "D"
                    .Font.Size = 16
                Case Else
                    .Font.Size = 8
            End Select
        End With
    Next cel
    
         
End Sub

 

[lrobbo314]

Of course to speed things up you can add

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

at the beginning of the code and

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

at the end.

 

[lrobbo314]

This version works as well and is a lot faster than the previous.

Tested on ~60,000 rows, the first code took 9 seconds to complete whereas the following code took 1 second.

Sub test2()
Dim r   As Range
Dim cel As Range
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Set r = Range("G2", Range("G" & Rows.Count).End(xlUp))

r.Font.Size = 8

r.AutoFilter Field:=1, Criteria1:="a", Operator:=xlOr, Criteria2:="b"
r.SpecialCells(xlCellTypeVisible).Font.Size = 16
r.AutoFilter Field:=1, Criteria1:="c", Operator:=xlOr, Criteria2:="d"
r.SpecialCells(xlCellTypeVisible).Font.Size = 16
r.AutoFilter

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

 

[bmcley]

Irobbo, that's awesome. Thank you sir. And thanks for the quick response.

One other question though, this is enlarging the size of the text for that column. Is there a way to enlarge the text of the entire row, or more specifically, columns A-H of that line?

Thanks again.

 

[lrobbo314]

Yes,

We're back to the slower version, but it should work.

Sub fSize()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Dim r   As Range
Dim cel As Range

Set r = Range("G1", Range("G" & Rows.Count).End(xlUp))

    For Each cel In r
        With cel.Offset(, -6).Resize(1, 8)
            Select Case cel
                Case "A"
                    .Font.Size = 16
                Case "B"
                    .Font.Size = 16
                Case "C"
                    .Font.Size = 16
                Case "D"
                    .Font.Size = 16
                Case Else
                    .Font.Size = 8
            End Select
        End With
    Next cel
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

 

[bmcley]

Nice! You rock. Thanks so much.

 

[bmcley]

Good morning lrobbo314,

Can you please help me take the above code and expand it to increase the size of text in the entire row, if I have an "A,B, or C" in either row G or row J.
It is currently working great for row G, but I have added another customer to the data, so that I can compare the two. I attempted to figure this out myself, but got some wonky results, where the entire row is not necessarily increase text size.

Thanks in advance for your help.