Hello,
I have conditionally formatted cells but when i change the value of the cell they will not change color until I hit F5. Is there a way I can autoupdate my sheet so the cells will change color as soon as the value changes?
I have conditionally formatted cells but when i change the value of the cell they will not change color until I hit F5. Is there a way I can autoupdate my sheet so the cells will change color as soon as the value changes?
VBA Code:
'!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!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 NewRow in the first Column
ActiveCell.Formula = NewNumber ' Gives Gives NewRow an updated number
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 'Selects YES cell
ActiveCell.Value = "YES" 'Now says YES
Sheet5.Cells(NewRow, 11).Select 'Selects NO cell
ActiveCell.Value = "NO" 'Now says NO
Sheet5.Cells(NewRow, 12).Select 'Selects N/A cell
ActiveCell.Value = "N/A" 'Now says N/A
Sheet5.Rows(NewRow).RowHeight = 28.8 'Set Row Height of the Question row
Sheet5.Rows(NewRow - 1).RowHeight = 3 'Set Height of the row above the question
'!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!Conditional Formatting
Sheet5.Cells(NewRow, 18).NumberFormat = ";;;" 'Makes numbers disappear
If Sheet5.Cells(NewRow, 18).Value = 1 Then 'If Yes
Sheet5.Cells(NewRow, 16).Interior.Color = xlNone 'Red is no fill
Sheet5.Cells(NewRow, 17).Interior.Color = xlNone 'Yellow is no fill
Sheet5.Cells(NewRow, 18).Interior.Color = 13561798 'Green is filled
ElseIf Sheet5.Cells(NewRow, 18).Value = 2 Then 'If No
Sheet5.Cells(NewRow, 16).Interior.Color = 13551615 'Red is filled
Sheet5.Cells(NewRow, 17).Interior.Color = xlNone 'Yellow is no fill
Sheet5.Cells(NewRow, 18).Interior.Color = xlNone 'Green is filled
ElseIf Sheet5.Cells(NewRow, 18).Value = 3 Then 'If N/A
Sheet5.Cells(NewRow, 16).Interior.Color = xlNone 'Red is no fill
Sheet5.Cells(NewRow, 17).Interior.Color = 10284031 'Yellow is filled
Sheet5.Cells(NewRow, 18).Interior.Color = xlNone 'Green is filled
End If
End Sub