Colour cell on click event

[Aussiebear]

This has been an extremely frustrating issue. In the attached workbook, I am looking for particular cells to either be coloured upon being clicked or un-coloured upon a second click.

The code provided seemingly does nothing, even after Saving, Closing, then reopening the file. These are the versions tried, and before anyone asks, all lines were commented out before trying the newest version.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim ws As Worksheet
    Dim targetCell As Range
    Set ws = ThisWorkbook.Sheets("Sheet1")
    If Not Intersect(Target, ws.Range("B4:N4")) Is Nothing Then
        Set targetCell = Target
        If targetCell.Interior.Color = ws.Range("B4").Interior.Color Then
            targetCell.Interior.Color = xlNone
        Else
            targetCell.Interior.Color = ws.Range("B4").Interior.Color
        End If
    End If
End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim targetCell As Range
    Dim orangeColor As Long
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")
    orangeColor = RGB(255, 165, 0) ' Explicit orange color
    If Not Intersect(Target, ws.Range("B4:N4")) Is Nothing Then
        Set targetCell = Target
        If targetCell.Interior.Color = orangeColor Then
            targetCell.Interior.Color = xlNone
        Else
            targetCell.Interior.Color = orangeColor
        End If
    End If
End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim targetCell As Range
    Dim orangeColor As Long
    Dim ws As Worksheet
    Dim targetRange As Range ' Explicitly declare Target
    Set ws = ThisWorkbook.Sheets("Sheet1")
    orangeColor = RGB(255, 165, 0)
    Set targetRange = Target ' Set targetRange
    If Not Intersect(targetRange, ws.Range("B4:N4")) Is Nothing Then
        Set targetCell = targetRange
        If targetCell.Interior.Color = orangeColor Then
            targetCell.Interior.Color = xlNone
        Else
            targetCell.Interior.Color = orangeColor
        End If
    End If
End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Call ToggleCellColor
End Sub

The ultimate aim is to allow the User to select the preferred months of the year for his/her area for each of the sectors in Range A4 to A9 as the criteria.

Colours used with colour Index #
White, Background 1, 15% darker (16)
Orange (48)
Plum, Accent 5 (56)
Turquoise, Accent 4 Lighter 40% (10)
Red (3)

I've had that mmany swings and misses, I can't tell if i've been struck out or thrown out.

[June7]

Does this help https://stackoverflow.com/questions/...ell-is-clicked

[rollis13]

@aussiebear, I'm not sure if this is just a mistake in reporting the example, but since the macro triggers via event (Worksheet_SelectionChange), the macro must be in the sheet's module, not in a standard module.
Your first macro seems to be the most appropriate (maybe it needs some fixing, the color test should be on A4 instead of B4).

[Paul_Hossler]

1. The event handler has to be in the appropriate worksheet module
2. You were looking for a match with B4 and I think you wanted A4
3. Personally, I'd use the DoubleClick event since ChangeSelection has too many ways to go wrong IMO
If A1 is ActiveCell and you click B5, the interior changes. BUT if you click B5 again (without selecting another cell) to change it back, the event handler doesn't fire and the color remains

Try this

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    MsgBox Target.Address
End Sub

(4. You spelt 'color' as 'colour' -- that'll mess you up every time )


Capture.JPG

Option Explicit
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim targetCell As Range, baseCell As Range
    
    Set targetCell = Target.Cells(1, 1)
    
    If Intersect(Target, Range("B4:N9")) Is Nothing Then Exit Sub
        
    Application.EnableEvents = False
        
    Set targetCell = Target
    Set baseCell = Cells(targetCell.Row, 1)
    
    If targetCell.Interior.ColorIndex = baseCell.Interior.ColorIndex Then
        targetCell.Interior.ColorIndex = xlColorIndexNone
    Else
        targetCell.Interior.ColorIndex = baseCell.Interior.ColorIndex
    End If

    Application.EnableEvents = True
End Sub

[Aussiebear]

Thank you Paul, June7 & Rollis13.

@ June7, Not really but its worth noting.

@ Rollis13, cant seem to do that on a Mac. If I rightclick on the Actual sheet and select insert Module it plonks it down as if you had asked from the Command bars at the top of the sheet.

Correction: It does if I click on View code.... I've been 26 hours without sleep and the brain has been sitting in idle it seems.

@ Paul. Absolutely perfect Paul. Except for your spelling..... english used to be a beautiful language, until the English themselves decided to muck it up, then heaven forbid the Americans got stuck into it.........

[Paul_Hossler]

Doesn't matter (much) but I just noticed that I left a bit of unnecessary code when I was playing around

Set targetCell = Target '<<<<<<<<<<<<<<<<<<<<
Set baseCell = Cells(targetCell.Row, 1)

[Aussiebear]

Discarded the wrong set of cards?

[georgiboy]

until the English themselves decided to muck it up

How could we have mucked it up when we created it?


I heard that Trump wants to officially recognise (not recognize ) 'American English' as its own language. Would it simply be called American?

[Aussiebear]

How could we have mucked it up when we created it?

How many english dialects are there across all of your counties? Years ago you sent Geoff Boycott out here and no one could understand him.

[georgiboy]

Point take but the only important one is the one I speak: cockney

[Paul_Hossler]

How could we have mucked it up when we created it?
I heard that Trump wants to officially recognise (not recognize ) 'American English' as its own language. Would it simply be called American?

1. That was the real reason back in 1776

2. Technically there's many different countries in the Americas (North and South) including our used to be friendly before we pissed them off neighbors to the north so he should be pushing 'United States of American English', but that's a mouthful

[georgiboy]

Ah yes, back in the days of King George III and Noah Webster’s spelling reforms

It is a mouthful indeed, 'American' does have a ring to it or maybe 'U.S. English'

[Aussiebear]

The American spelling is double dutch to me

[Paul_Hossler]

The American spelling is double dutch to me

TBH, I have a hard time with a couple (OK, many) words regardless of the language

I edit a lot of ePub books just for myself and many use lang="en-GB" so I eventually decided to just spell check using a British dictionary.

Yes, it looks a little 'double dutch' to me, but it's a lot less work