Macro to change font size automatically for specific cell based on value

nicholasmarella

New Member
Joined
Feb 10, 2020
Messages
5
Office Version
  1. 2019
Platform
  1. Windows
Hello,

I will try to explain this as best as possible without attaching pictures (forum says "not a picture" at the top - even so, each time I screen shot, it comes out extremely blurry so it's essentially useless regardless). I have a group of cells merged together (G11 through I17) that uses a formula to either return a 0 through 11 (numerical) result or a "SOP Error" result. When the numerical value shows, the font is 24 which is exactly what I need. When the SOP Error value shows, it remains 24 and I need it to change to 12 to fit properly. The numerical value comes back based on a series of check boxes being checked. The SOP error comes back and essentially overrides the numerical value when a specific check box, separate from the others related to the numerical value, is checked.

I need a macro that will automatically changed the font size to 12 when the SOP Error value is returned but remains at 12 for any other value. I've tried googling and copying and pasting various macro codes I've found online but nothing seems to work. I'm not sure if I'm just doing something wrong when it comes to pasting the code and expecting it to begin working by itself, or if there's something wrong with the codes I'm finding online. Any help would be greatly appreciated!

A few codes I've tried online;
Private Sub Worksheet_Calculate()
Dim rng As Range
Dim rCell As Range

Set rng = Range("A1:A10")

For Each rCell In rng
If Len(rCell.Text) > 2 Or _
Val(rCell.Value) > 10 Then
rCell.Font.Name = "Arial"
rCell.Font.Size = 16
Else
rCell.Font.Name = "Times New Roman"
rCell.Font.Size = 12
End If
Next
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rCell As Range

If Union(Target, Range("A1:A10")).Address = _
Range("A1:A10").Address Then
Application.EnableEvents = False
For Each rCell In Target
If Len(rCell.Text) > 2 Or _
Val(rCell.Value) > 10 Then
rCell.Font.Name = "Arial"
rCell.Font.Size = 16
Else
rCell.Font.Name = "Times New Roman"
rCell.Font.Size = 12
End If
Next
Application.EnableEvents = True
End If
End Sub

FYI, I'm not the most familiar with macros and the lingo as well. With that said, I may need any responses broken down into something I can easily understand lol.

Also, if there's any more information I'm supposed to put in this thread, please let me know.
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,069
Office Version
  1. 2013
Platform
  1. Windows
@nicholasmarella It may just be me but, I am finding your description and the codes a little confusing / contradictory.
Can you please clarify.
You have a merged cell G11:I17 which contains a formula?
Formula returns number 0 thro 11 or text "SOP Error" ?
You want 24 font when number or 12 font when text ?

Is the reference to range A1:A10 in the sample code relevant and if so, how ?
Are you wanting to actually switch font style, as per the example code?
 

nicholasmarella

New Member
Joined
Feb 10, 2020
Messages
5
Office Version
  1. 2019
Platform
  1. Windows
@Snakehips Sorry for any confusion. The range in the sample code is irrelevant. I was just posting codes I've tried. When I used those codes, I replaced them with G11 since it's the first cell in the group of merged cells. The merged cell contains a formula that returns two different types of results. It could return a numerical value (0-11) or it could return a text value (SOP Error). I do want 24 font when it's the numerical value and 12 font when it's the text value. I don't want to change the font style, just the font size.
 

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,069
Office Version
  1. 2013
Platform
  1. Windows
Then maybe just a s simple as..
VBA Code:
Private Sub Worksheet_Calculate()

Dim rCell As Range
Set rCell = Range("G11")
    With rCell
        If .Value = "SOP Error" Then
        
                .Font.Size = 12
        Else
        
                .Font.Size = 24
        End If
    
    End With

End Sub

Hope that helps.
 

nicholasmarella

New Member
Joined
Feb 10, 2020
Messages
5
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

@Snakehips It worked!!!!! Thank you!!!! I've been trying to figure this out since Friday lol. One last question for you. I need to do this for multiple ranges. Can i just copy and paste the code for as many ranges as I need? FYI, each range will show it's own value based on the check boxes.
 

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,069
Office Version
  1. 2013
Platform
  1. Windows
(y)
The short answer is yes. You only can have the one Worksheet_Calculate event code per sheet so if you have multiple things to test / action then the associated code needs to be included.
Depending on how many more ranges you have and what your tests and actions are, there are probably more efficient ways of coding it than just duplicating the above with different range values.
If you can supply more detail of what else you are wanting to achieve then I will help if I can.
 

nicholasmarella

New Member
Joined
Feb 10, 2020
Messages
5
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

@Snakehips Well, there's additional ranges that are essentially the same exact thing as the G11. They're just in different spots on the same sheet. G23, G35, G47, G59, G71, G83, G95, G107, G119, G131, G143, G155, G167.
 

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,069
Office Version
  1. 2013
Platform
  1. Windows
Try...

VBA Code:
Private Sub Worksheet_Calculate()
Dim rCell As Range
Dim r As Integer

For r = 11 To 167 Step 12
    Set rCell = Range("G" & r)
        With rCell
            If .Value = "SOP Error" Then            
                    .Font.Size = 12
            Else            
                    .Font.Size = 24
            End If
        
        End With
Next r
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,122,316
Messages
5,595,448
Members
413,993
Latest member
CSEGUIN1973

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top