ipbr21054
Well-known Member
- Joined
- Nov 16, 2010
- Messages
- 5,226
- Office Version
- 2007
- Platform
- Windows
Morning,
On my userform i have two option buttons.
Basically they enter YES / NO into the row in question at column J
I would like the cell to be coloured.
So NO would be Red & YES would be Green.
I need some advice on how to go about this whether its in this part of code supplied or a worksheet change where the code would look for a value of YES or NO then interior cell as mentioned.
The current working code in shown below.
On my userform i have two option buttons.
Basically they enter YES / NO into the row in question at column J
I would like the cell to be coloured.
So NO would be Red & YES would be Green.
I need some advice on how to go about this whether its in this part of code supplied or a worksheet change where the code would look for a value of YES or NO then interior cell as mentioned.
The current working code in shown below.
Rich (BB code):
Private Sub CommandButton1_Click()
If Len(Me.TextBox2.Value) = 17 Then
Dim i As Integer
Dim x As Long
Dim ControlsArr(1 To 8) As Variant
For i = 1 To 8
If i > 2 Then
'comboboxes
ControlsArr(i) = Me.Controls("ComboBox" & i).Value
Else
'textboxes
ControlsArr(i) = Me.Controls("TextBox" & i).Value
End If
Next i
Application.ScreenUpdating = False
With ThisWorkbook.Worksheets("MC LIST")
.Range("A8").EntireRow.Insert Shift:=xlDown
.Range("A8:J9").Borders.Weight = xlThin
.Cells(8, 1).Resize(, UBound(ControlsArr)).Value = ControlsArr
.Cells(8, 2).Characters(Start:=10, Length:=1).Font.Color = -16776961
.Cells(8, 9).Value = GetYear(Mid(.Cells(8, 2).Value, 10, 1))
If OptionButton1.Value = True Then .Cells(8, 10).Value = "YES": OptionButton1.Value = False
If OptionButton2.Value = True Then .Cells(8, 10).Value = "NO": OptionButton2.Value = False
End With
Range("B8").Select
Range("A8").Select
ActiveWorkbook.Save
Application.ScreenUpdating = True
MsgBox "Database Has Been Updated", vbInformation, "SUCCESSFUL MESSAGE"
With ThisWorkbook.Worksheets("MC LIST")
If .AutoFilterMode Then .AutoFilterMode = False
x = .Cells(.Rows.Count, 1).End(xlUp).Row
.Range("A7:I" & x).Sort Key1:=Range("A8"), Order1:=xlAscending, Header:=xlGuess
.Range("B8").Select
.Range("A8").Select
End With
Unload McListForm
Else
MsgBox "VIN MUST BE 17 CHARACTERS" & vbCr & vbCr & "DATABASE WAS NOT UPDATED", vbCritical, "MC LIST TRANSFER"
TextBox2.SetFocus
End If
End Sub