I have this code that changes the background color of a text box based on the number in a cell. It works but it's long. I have 40 text boxes on the sheet that need to run this code as well. Any ideas?
Sub change_box1_color()
'
' change_box1_color Macro
'
'
If Range("F14").Value = 1 Then
ActiveSheet.Shapes("TextBox 259").Fill.ForeColor.RGB = RGB(255, 255, 255)
Else
If Range("F14").Value = 2 Then
ActiveSheet.Shapes("TextBox 259").Fill.ForeColor.RGB = RGB(51, 51, 255)
Else
If Range("F14").Value = 3 Then
ActiveSheet.Shapes("TextBox 259").Fill.ForeColor.RGB = RGB(255, 0, 0)
Else
If Range("F14").Value = 4 Then
ActiveSheet.Shapes("TextBox 259").Fill.ForeColor.RGB = RGB(51, 204, 51)
Else
If Range("F14").Value = 5 Then
ActiveSheet.Shapes("TextBox 259").Fill.ForeColor.RGB = RGB(255, 255, 204)
Else
If Range("F14").Value = 6 Then
ActiveSheet.Shapes("TextBox 259").Fill.ForeColor.RGB = RGB(255, 255, 255)
Else
If Range("F14").Value = 7 Then
ActiveSheet.Shapes("TextBox 259").Fill.ForeColor.RGB = RGB(255, 255, 0)
Else
If Range("F14").Value = 8 Then
ActiveSheet.Shapes("TextBox 259").Fill.ForeColor.RGB = RGB(255, 192, 0)
Else
If Range("F14").Value = 9 Then
ActiveSheet.Shapes("TextBox 259").Fill.ForeColor.RGB = RGB(0, 0, 0)
Else
ActiveSheet.Shapes("TextBox 259").Fill.ForeColor.RGB = RGB(255, 255, 255)
End If
End If
End If
End If
End If
End If
End If
End If
End If
End Sub
Sub change_box1_color()
'
' change_box1_color Macro
'
'
If Range("F14").Value = 1 Then
ActiveSheet.Shapes("TextBox 259").Fill.ForeColor.RGB = RGB(255, 255, 255)
Else
If Range("F14").Value = 2 Then
ActiveSheet.Shapes("TextBox 259").Fill.ForeColor.RGB = RGB(51, 51, 255)
Else
If Range("F14").Value = 3 Then
ActiveSheet.Shapes("TextBox 259").Fill.ForeColor.RGB = RGB(255, 0, 0)
Else
If Range("F14").Value = 4 Then
ActiveSheet.Shapes("TextBox 259").Fill.ForeColor.RGB = RGB(51, 204, 51)
Else
If Range("F14").Value = 5 Then
ActiveSheet.Shapes("TextBox 259").Fill.ForeColor.RGB = RGB(255, 255, 204)
Else
If Range("F14").Value = 6 Then
ActiveSheet.Shapes("TextBox 259").Fill.ForeColor.RGB = RGB(255, 255, 255)
Else
If Range("F14").Value = 7 Then
ActiveSheet.Shapes("TextBox 259").Fill.ForeColor.RGB = RGB(255, 255, 0)
Else
If Range("F14").Value = 8 Then
ActiveSheet.Shapes("TextBox 259").Fill.ForeColor.RGB = RGB(255, 192, 0)
Else
If Range("F14").Value = 9 Then
ActiveSheet.Shapes("TextBox 259").Fill.ForeColor.RGB = RGB(0, 0, 0)
Else
ActiveSheet.Shapes("TextBox 259").Fill.ForeColor.RGB = RGB(255, 255, 255)
End If
End If
End If
End If
End If
End If
End If
End If
End If
End Sub