Change cell font size determined by other cell result

[Deliverable7]

Hi

I've been scouring the net for a solution to my simple (haha they are always simple) font size problem but can find what i'm looking for!

Can someone please help me with:

• If the value in A2 = 1 then in cell A1 change the font colour to red, size to 10 and unbold.
• Else if the value A2 = 0 then in cell A1 change the font colour to dark blue size to 16 and bold.

The code needs to auto run whenever the value in cell A2 changes.

Thanks

 

[mole999]

simplish custom format, set A2 to be 16 , blue and Bold
then A2 set conditional format for 1 and make that Red, size 10

 

[mohadin]

Hi,
You mean

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Range("A2"), Target) Is Nothing Then
        With Selection.Offset(-1, 0)
            If Target.Value = 1 Then
                .Font.Size = 10
                .Font.Color = vbRed
                .Font.Bold = False
            Else
                If Target.Value = 2 Then
                    .Font.Size = 16
                    .Font.Color = vbBlue
                    .Font.Bold = True
                End If: End If
        End With
    End If
End Sub

 

[Deliverable7]

Thanks mohadin.
Your code works well but relies on clicking on the target cell to enact the change. The value in the target cell is derived from a formula with data entered elsewhere in the sheet. Can the code be triggered by picking up the changing value in the target cell i.e. without selecting the target cell?
Thanks

 

[mohadin]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    With Range("a1")
        If Target.Offset(-1).Value = 1 Then
            .Font.Size = 10
            .Font.Color = vbRed
            .Font.Bold = False
        Else
            If Target.Offset(-1).Value = 2 Then
                .Font.Size = 16
                .Font.Color = vbBlue
                .Font.Bold = True
            End If
        End If
    End With

End Sub

 

[Deliverable7]

Thanks mohadin.
It works exactly as i was looking for.
However, there are a couple of errors;
1. if i select any cell in row 1 i get: Application-defined or object-defined error, and
2. if i select any two cells (outside of row 1) i get: Type mismatch
Are you able to assist?
Thanks

 

[mohadin]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, Range("a:a")) Is Nothing Then Exit Sub
    If Target.Column = 1 Then
        If Target.Count > 1 Then Exit Sub
        With Cells(1, 1)
            If Target.Offset(-1).Value = 1 Then
                .Font.Size = 10
                .Font.Color = vbRed
                .Font.Bold = False
            Else
                If Target.Offset(-1).Value = 2 Then
                    .Font.Size = 16
                    .Font.Color = vbBlue
                    .Font.Bold = True
                End If: End If
        End With
    End If
End Sub

 

[Deliverable7]

Hi mohadin.
We are getting closer but still have the following issues;
1. if i select cell A1 i get the error message Application-defined or object-defined error, and
2. The macro runs if i manually change the value in cell A2. However what i'm looking for is whenever cell C2 returns a different value than it currently has, based on its FORMULA, NOT based on manually typing a different value.
Thanks for your patience with this.

 

[mohadin]

Hi
what about

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A2")) Is Nothing Then Exit Sub
    If Target.Column = 1 Then
        If Target.Count > 1 Then Exit Sub
        With Cells(1, 1)
            If Target.Value = 1 Then
                .Font.Size = 10
                .Font.Color = vbRed
                .Font.Bold = False
            Else
                If Target.Value = 2 Then
                    .Font.Size = 16
                    .Font.Color = vbBlue
                    .Font.Bold = True
                End If: End If
        End With
    End If
End Sub

 

[Deliverable7]

Hi mohadin
With your direction i was able to get to where i wanted;

Private Sub Worksheet_Calculate() '(ByVal Target As Range)
    'If Intersect(Target, Range("A2")) Is Nothing Then Exit Sub
    'If Range("A2") = 1 Then
        If Worksheets("InputLists").Range("ValidateForm") > 2 Then Exit Sub
        With Worksheets("Initiation").Range("ProjectDescriptor")
            If Worksheets("InputLists").Range("ValidateForm") = 1 Then
                .Font.Size = 10
                .Font.Color = vbRed
                .Font.Bold = False
            Else
                If Worksheets("InputLists").Range("ValidateForm") = 0 Then
                    .Font.Size = 16
                    .Font.Color = vbBlue
                    .Font.Bold = True
                End If: End If
        End With
    'End If
End Sub

Its a means to get around the limitations of Conditional Formatting. A formula produces a length error message hence the need to reduce the font size and colour to red.
Thanks for your assistance.