Change font size in row based on cell value within specific column

I'm attempting (unsuccessfully) to create a macro that will allow me to edit the font size, color, and bold/not bold formats of a range of cells within one row based on the specific value of a cell within that range. Can anyone help? I'd very much appreciate the assistance.

Thank you in advance.

Hi

How about this? Enter the value 1 in cell a1, put some other values in the other cells in row 1, and run macro....

Sub ChangeCellColor()

Dim MyRange1 As Range
Dim MyRange2 As Range


Set MyRange1 = Range("a1")
Set MyRange2 = Application.Rows(1)



If MyRange1.Value = 1 Then MyRange2.Font.Color = RGB(255, 0, 0)


End Sub

Hi,

Or maybe something like this on the Worksheet Change event.

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.ScreenUpdating = False
    Set Target = Range("A1")
    Set MyRange = Application.Rows(1)
    If Target.Value = 1 Then
        MyRange.Font.Bold = True
        MyRange.Font.ColorIndex = 3
    Else: MyRange.Font.Bold = False
        MyRange.Font.ColorIndex = 10
    End If
    Application.ScreenUpdating = True
End Sub

Steve

This works, but returns an error when I change the range to include multiple rows. Can this be edited to skip rows in which the target.value does not = 1?

How about this,

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim x As Long
        For x = Range("A" & "20000").End(xlUp).Row To 1 Step -1
                        If Cells(x, 1).Value = 1 Then Application.Rows(x).Font.Bold = True Else: Application.Rows(x).Font.Bold = False
        Next x
        
    End Sub

In all honesty I can't tell. Besides that, I need the row's font: size, color, and bold to be determined by the value of this cell.

The function below achieves exactly what I want, but only applies to column G. Can demonstrate how to edit it to apply the formatting changes to columns other than G while still being dependent on only the values within column G?


Private Sub Worksheet_Change(ByVal Target As Range)

Dim r As Range, c As Range

Set r = Worksheets("sheet 1").Range("C220:P2000")

For Each c In r
With c
If Not IsError(.Value) Then
Select Case .Value

Case "TOTALS - - >"
.Font.ColorIndex = 1
.Font.Bold = True
.Font.Size = 20

Case "SUBTOTALS - - >"
.Font.ColorIndex = 1
.Font.Bold = True
.Font.Size = 15

End Select
End If
End With
Next c

End Sub


I very much appreciate all of your efforts with this, thank you.

Hi,

What do "TOTALS-->" and "SUBTOTALS-->" represent?

Steve

Those are the values in the cells that will drive the formatting change. i.e. when the cells contain those values, the formatting should automatically change.

Hi,

Here's a version of mine modified. Just wondering though why not just use conditional formatting?

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim x As Long
    Application.ScreenUpdating = False
    For x = Range("G" & "20000").End(xlUp).Row To 1 Step -1
        If Cells(x, 7).Value = "TOTALS - - >" Then
            Application.Rows(x).Font.Color = 1
            Application.Rows(x).Font.Bold = True
            Application.Rows(x).Font.Size = 20
        ElseIf Cells(x, 7).Value = "SUBTOTALS - - >" Then
            Application.Rows(x).Font.Color = 1
            Application.Rows(x).Font.Bold = True
            Application.Rows(x).Font.Size = 15
        Else
            Application.Rows(x).Font.Color = 3
            Application.Rows(x).Font.Bold = True
            Application.Rows(x).Font.Size = 10
        End If
    Next x
 Application.ScreenUpdating = True
End Sub

\

HTH
Steve

The solution also only appears to be changing the font size and bold/not bold; the color for some reason remains unchanged. ???

Can this be edited to affect only a specific range of columns?

I'm not using conditional formatting because I would like the font size changed and as far as I know that can't be accomplished in conditional formatting.

I've input your solution here and it worked, but when I change some of the font colors, i.e. from 1 to 3, I can't get it to run again. Will this macro be something that can continuously run and update my sheet as often as cell values change?

Please forgive the naivete.

Thanks

I've since been able to solve this problem utilizing a case structure. Thank you all for the help.

Hey all! I came across this forum because I'm looking for a similar solution, and I know NOTHING about writing macros.
I want what you might consider to be a very simple macro, and very similar to what I see here (I think). Please please PLEASE help.

I want the size of each ROW to be twice as much as the value in COLUMN D.
Column D currently has all values assigned as a formula, Text Formatted as a Currency. If this is a problem, I can change it easily. It doesn't HAVE TO be a formula, that just makes it easy for me. It doesn't HAVE TO be currency, that's completely optional.

You might just have to say "the font of ColumnD(x) = ColumnD(x)*2", but in the proper terms.
I don't know.
Anyway, that's all I need, and I'm baffled just reading other people's scripts.

Can you guys help me? Please please please?