Conditional Formatting VBA

[riedyp]

Hello,

I am trying to do some simple conditional formatting to change the colors of some cells based off the value of one of the others. My "NewRow" variable works for statements before my If statement so i know thats not it. The cell "(NewRow,18)" will be related to a radio button later on so that is why everything will be related to that cell. Please let me know of any suggestions you may have.

'!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!Button to Add Question
'Add New Question Formatting
Private Sub CommandButton1_Click()
NewRow = Sheet5.Cells(1000, 2).End(xlUp).Row + 2 'Finds the last edited row and then adds two rows
NewNumber = Sheet5.Cells(1000, 1).End(xlUp).Value + 1 'Finds the last question number and adds 1
 Sheet5.Cells(NewRow, 1).Select ' Selects LastRow in the first Column
    ActiveCell.Formula = NewNumber ' Gives it LastNumber
Sheet5.Range("B" & NewRow).Resize(, 11).Interior.Color = RGB(155, 194, 230) 'Changes color for question
Sheet5.Range("B" & NewRow).Resize(, 8).MergeCells = True 'Merges question cells
Sheet5.Range("M" & NewRow).Resize(, 3).MergeCells = True 'Merges answer cells
Sheet5.Cells(NewRow, 10).Select 'YES
 ActiveCell.Value = "YES"
Sheet5.Cells(NewRow, 11).Select 'NO
 ActiveCell.Value = "NO"
 Sheet5.Cells(NewRow, 12).Select 'N/A
 ActiveCell.Value = "N/A"
Sheet5.Rows(NewRow).RowHeight = 28.8 'Set Row Height
Sheet5.Rows(NewRow - 1).RowHeight = 3 'Set Predecessor Row Height
'!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!Conditional Formatting
If Sheet5.Cells(NewRow, 18).Value = 1 Then 'Green box
Sheet5.Cells(NewRow, 16).Interior.ColorIndex = 0 ' Red is no fill
Sheet5.Cells(NewRow, 17).Interior.ColorIndex = 0 ' Yellow is no fill
Sheet5.Cells(NewRow, 18).Interior.ColorIndex = 13561798 'Green is filled
ElseIf Sheet5.Cells(NewRow, 18).Value = 2 Then
Sheet5.Cells(NewRow, 16).Interior.ColorIndex = 13551615 ' Red is filled
Sheet5.Cells(NewRow, 17).Interior.ColorIndex = 0 ' Yellow is no fill
Sheet5.Cells(NewRow, 18).Interior.ColorIndex = 0 'Green is filled
ElseIf Sheet5.Cells(NewRow, 18).Value = 3 Then
Sheet5.Cells(NewRow, 16).Interior.ColorIndex = 0 ' Red is no fill
Sheet5.Cells(NewRow, 17).Interior.ColorIndex = 10284031 ' Yellow is filled
Sheet5.Cells(NewRow, 18).Interior.ColorIndex = 0 'Green is filled
End If
End Sub

 

[Fluff]

Change ColorIndex to Color & change the 0s to xlNone

 

[riedyp]

Thank you, that worked.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[riedyp]

When I change the value in (NewRow,18) I have to hit F5 for the value to change. Is there a way to have the sheet auto update so i wont have to hit F5?

 

[Fluff]

That's a new question, so needs a new thread please