Auto update conditionally formatted cells when changed

[riedyp]

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?

'!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!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

 

[MARK858]

Turn Auto calculation back on (on the formulas tab).

 

[riedyp]

Turn Auto calculation back on (on the formulas tab).

I currently have Auto Calculation on and it does not update.

 

[MARK858]

Then do you have any formulas with circular references?

 

[riedyp]

I do not believe so, all of my code is above with the conditions only being dependent on a singular cell.

 

[MARK858]

I am not referring to the conditional formatting but rather if you have any normal formulas with circular references which might prevent calculation happening.

I am also a bit confused as you state it doesn't update until you press F5, but F5 brings up the Goto window.
Does this mean that you are still within the cell in Edit mode (I must admit I can't replicate this as I can't bring up the Goto window in edit mode)?
To do a manual calculate you would normally use F9 to force a calculation.

Just to be clear what you have in your code isn't conditional formatting but normal formatting which would need to be triggered by the button click.
If it is that code that you want to run automatically then you would need to put it in a Worksheet event.

 

[riedyp]

Oh okay. The only way I get the formatting to change is when I have the code opend and I hit F5 to run the code. If I hit F9 with only the sheet up nothing happens. Well
I do not want to have to click the button to have these cells be formatted, if I just create a new "Private Sub" that is outside "CommandButton1_Click()" then would it change these values automatically without hitting a button?

 

[MARK858]

It would if you used a Worksheet_Change event code but would need a bit of re-coding. Why can't you use proper conditional formatting?

 

[riedyp]

I realize now that I can easily just use normal conditional formatting. Thank you.