QColour interior cell advice

[ipbr21054]

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.

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

 

[ipbr21054]

I thought something like this BUT

It needs to be J8 then down the page NOT J8:J100

Also it needs to work as the below doesnt ?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRange As Range
Dim myCell As Range
Set myRange = Range("J8:J100")
For Each myCell In myRange
If myCell.Value = "YES" Then
myCell.Font.Colour = vbRed = True
Else
If myCell.Value = "NO" Then
myCell.Font.Colour = vbGreen = True

End If
Next myCell
End Sub

 

[sykes]

Ian

Easiest way is ditch VBA for this - just use conditional formatting on J8, and set the fill to Red or green, depending upon no/yes.

 

[sykes]

...your original post:

If OptionButton1.Value = True Then .Cells(8, 10).Value = "YES": OptionButton1.Value = False

... suggested that you only wanted the formatting on J8, however, I see now that you want it much further down the column.
If you've already conditionally formatted J8 (like I had) simplest way to continue, would be to just select the format painter, select J8, then wipe right down the J col until you've done enough, or got fed up!

 

[ipbr21054]

Hi,
I would like to continue with vba.
The range is J8 then down the page

 

[ipbr21054]

So can we look at just cell J8 then.
When I complete the form the cell will either be YES or NO so at this point the FONT needs to be applied.

I know I’m close to getting it right in my code BUT just missing something to complete it.

 

[Fluff]

Cross posted Option button = true then font colour to be changed - OzGrid Free Excel/VBA Help Forum

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[ipbr21054]

OK
Didnt know that but was looking for a different approach,thanks