Affecting adjacent cells on doubleclick event 1

[davedave24]

Hello

I have a column (H) that is formatted to Wingdings font. When the user double-clicks, it inserts a tick (Wingdings = ü).
There is also a conditional format on the column - if the cell is not empty (ticked), it is white; if empty, it is yellow.

How do I make the adjacent 3 cells also change between yellow/white at the same time? I've tried conditional format, and coding it, but have had no luck.

e.g. H12 is blank (yellow) - E12,F12,G12 are also yellow. User double-clicks in H12, a tick is inserted, cell goes white. E12, F12 and G12 now also go white.

Here is my tick code:

'Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
  Application.EnableEvents = False
  If Not Intersect(Target, Range("H9: H5000 ")) Is Nothing Then
    Select Case Target.Value
        Case "ü"
            'TICKED
            Target.Value = ""
            
        Case Else
            'BLANK - should already be yellow
            Target.Value = "ü"
           
        End Select
  End If

  Application.EnableEvents = True
  Cancel = True
  
End Sub

 

[SkipVought]

Hi

Select ALL the cells in all the rows you need to CF.

Open the CF Wizzard

Make your formula ABSOLUTE to column H like
[tt]
=$H2<>""
[/tt]
assuming row 2 is the top row of the CF rows selected.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[davedave24]

Brilliant, worked perfectly

Thanks