Change cell format in a sheet according to Option Button in a form

I have a database and a form to enter data to it. The form is adding data to the next empty row in a table. I have 4 Option Button in my form indicating which type of transaction is the next entry. I would like to format the background of the cell in column B depending on which option button is selected so when I click on confirm, the data from the form is inserted in the database and the background color of cell in column B is set properly. I can't upload my code from this device but actually the background color is set but is always the same and does not change if I select an other option button.

Any idea what could be the problem? Do I need to include a line to clear previous formatting prior to applying the option button one?

Private Sub CommandButton1_Click()
Dim L As Integer
Dim Code As String

If MsgBox("Confirm?", vbYesNo, "Confirming new invoice") = vbYes Then
    L = Sheets("FACTURE").Range("D65535").End(xlUp).Row + 1 'Pour placer le nouvel enregistrement _ la premi_re ligne de tableau non vide
    Range("C" & L).Value = (Now)
    Range("D" & L).Value = TextBox2
    Range("E" & L).Value = TextBox3
    Range("F" & L).Value = TextBox4
    Range("G" & L).Value = TextBox5
    Range("K" & L).Value = ComboBox1
    Range("L" & L).Value = ComboBox2
    Range("M" & L).Value = ComboBox3
    Range("N" & L).Value = TextBox9
    Range("O" & L).Value = TextBox10
    Range("R" & L).Value = TextBox39
    Range("P" & L).Value = TextBox40
End If

If OptionButton1.Enabled = True Then
Range("B" & L).Select
 With Selection.Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .ThemeColor = xlThemeColorAccent3
    .TintAndShade = 0.399975585192419
    .PatternTintAndShade = 0
 End With
 
 ElseIf OptionButton2.Enabled = True Then
 Range("B" & L).Select
  With Selection.Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .ThemeColor = xlThemeColorAccent1
    .TintAndShade = 0.399975585192419
    .PatternTintAndShade = 0
 End With
 
End If

End Sub

Solution

you must use simply:

If OptionButton1 Then

...

ElseIf OptionButton2 Then

...

End If

because Enabled property of userform controls "Specifies whether a control can receive the focus and respond to user-generated events."

in other words it greys or blacken the control in the userform to make it, correspondingly, unavailable or available for user input

while you'd use it `Value' property that "determines or specifies whether or not the specified option button is selected"

and since OptionButton's Value property is the default one, you can omit it altogether

furthermore you may want to consider the following little refactoring of your code:

Option Explicit

Private Sub CommandButton1_Click()
    Dim L As Long
    Dim Code As String
    Dim TextBox2 As Long

    If MsgBox("Confirm?", vbYesNo, "Confirming new invoice") = vbYes Then
        With Worksheets("FACTURE")
            L = .Range(.Rows.Count, "D").End(xlUp).Row + 1 'Pour placer le nouvel enregistrement _ la premi_re ligne de tableau non vide
        End With
        With Me
            Range("C" & L).Value = (Now)
            Range("D" & L).Value = .TextBox2
            Range("E" & L).Value = .TextBox3
            Range("F" & L).Value = .TextBox4
            Range("G" & L).Value = .TextBox5
            Range("K" & L).Value = .ComboBox1
            Range("L" & L).Value = .ComboBox2
            Range("M" & L).Value = .ComboBox3
            Range("N" & L).Value = .TextBox9
            Range("O" & L).Value = .TextBox10
            Range("R" & L).Value = .TextBox39
            Range("P" & L).Value = .TextBox40

            If .OptionButton1.Enabled Then
               FormatCell Range("B" & L), xlThemeColorAccent3
            ElseIf .OptionButton2 Then
               FormatCell Range("B" & L), xlThemeColorAccent1
            End If
        End With
    End If
End Sub

Sub FormatCell(rng As Range, thColor As XlThemeColor)
    With rng.Interior
       .Pattern = xlSolid
       .PatternColorIndex = xlAutomatic
       .themeColor = thColor
       .TintAndShade = 0.399975585192419
       .PatternTintAndShade = 0
    End With
End Sub

where:

code gets executed only after user confirmation
I used a more conventional (and "state of the art") mode to get the first non empty row of a given worksheet column

which, together with the declaration of L as of Long type, makes it excel versioning independent, enabling it to get to the bottom of post Excel 2003 worksheets rows
I used With Me block for accessing userform controls through the dot (.) notation

this not only facilitates your coding of userform properties and methods (after the typing the dot Intellisense provides you with their list) but it also avoids possible shadowing of them.

the following code should explain you why:
```
Option Explicit

Private Sub CommandButton1_Click()
    Dim TextBox2 As Long

    TextBox2 = 2
    Range("D3").Value = TextBox2 '<-- you're referring to 'TextBox2' Long variable value
    Range("D3").Value = Me.TextBox2 '<-- you're referring to the userform control named after "TextBox2"
End Sub
```
I factorized-out the code responsible for formatting the cell to a specific Function FormatCell()

every time you see some duplication of code then it's time to write a Sub or Function to handle it with proper parameters

decoupling different tasks leads to a more sustainable and fast code debugging and maintenance