Automatic Conditional Formatting

[smitpau]

Hi,

I'd appreciate some help please with the below.

The idea is to have only ever one cell selected with "Yes", so that if the cell with "Yes" selected changes the previous cell with "Yes" becomes a "No"

I've attempted something like this below, but am struggling to change the old "Yes" to a "No"

Toggle Test.xlsb

1drv.ms

Thanks for reading

PS appreciate the title is slightly misleading but fixing the old "Yes" to turn to "No" will mean the conditional formatting then fully works.

 

[jasonb75]

Conditional formatting can only change the appearance (formatting) of the cells, it can not change what is in them.

Most likely, you will need a worksheet change event (vba) however there is insufficient information in your post for me to provide the code that you will need.

Most users of this forum are unable to download attached files due to security restrictions, those that are able to are often unwilling, especially those uploaded by members who are not well known within the community.

 

[Joe4]

That really isn't Conditiona Formatting. Conditional Formatting only changes the format of a cell, not its actual contents.
It sounds like what you need is a Worksheet_Change event procedure, which is code that runs automatically upon some cell being updated.
Please let us know what ranges that this applies to, and we can help you set that up.

EDIT: Looks like Jason and I on thinking on the same wavelength!

 

[smitpau]

Yes thanks both appreciate the title is wrong but was too late to change it.

The code would be that when one cell says "Yes" the other cells say "No".

So for example say range A1:A3 A1 was "Yes" so A2:A3 are "No", if A2 becomes "Yes" then A1 and A3 become "No".

Hope that is clear.

Thanks

 

[Joe4]

Right-click on the sheet tab name at the bottom of the screen, select View Code, and paste this VBA code in the resulting VB Editor window:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim cell As Range
    Dim rng As Range
    
'   Enter range to apply this code to
    Set rng = Range("A1:A3")
    
'   Only run if a single cell has been manually updated
    If Target.CountLarge > 1 Then Exit Sub
    
'   Check to see if update is made in the desired range
    If Intersect(Target, rng) Is Nothing Then Exit Sub
    
'   Check to see if updated cell value is "Yes"
    If Target = "Yes" Then
        Application.EnableEvents = False
'       Loop through range
        For Each cell In rng
            If cell.Address <> Target.Address Then cell = "No"
        Next cell
        Application.EnableEvents = True
    End If
    
End Sub

This should automatically do what you want in the range A1:A3.

 

[smitpau]

Haha thanks does the job perfectly, very cool.

Also appreciate the annotations in the code helps me to follow and learn.

 

[Joe4]

You are welcome.
Let me know if you have any questions about any of the particulars of the code, and I will be happy to answer them for you.