Conditional Format cells that have data validation

[Mandy_]

Hi

I am using data validation in cell M5 and also O8 (numeral starting at zero/0).

I would like to write a conditional format that if any change is made to cell M5, that O8 is 0 (which is an option in the drop down list). If conditional format is the right way to solve this? Otherwise I'm open to your suggestions (just not VBA if possible please).

My aim is that once cell M5 is selected, then O8 can be changed from the drop down list. But when M5 is changed, then O8 is zero/0. Blank might also work, but blank isn't in the drop down list. O8's value links to another cell, which is why I want the resulting value to be 0 when M5 is changed.

Thank you for your time,

 

[6StringJazzer]

There is no way for a worksheet formula to detect that a cell value has changed. This requires VBA. It is actually quite simple in VBA.

This code must be placed into the module for the sheet containing the cells.

Private Sub Worksheet_Change(ByVal Target As Range)

   Application.EnableEvents = False
   
   If Target.Address = "$M$5" Then [O8] = 0
   
   Application.EnableEvents = True

End Sub

BTW even if it were a way to detect a change without VBA, CF would be the wrong approach. The only way CF could be used to show a 0 in a cell is to apply a custom format of "0" based on a condition. However, this does not affect the underlying value, only what is displayed. CF cannot be used to change the actual value.