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

[nicholasmarella]

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.

 

[Snakehips]

@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]

@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]

Then maybe just a s simple as..

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]

@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]

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]

@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]

Try...

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

 

[nicholasmarella]

@Snakehips Worked perfectly!!!! Thank you.