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/33113275/action-when-a-cell-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 :devil2::rotlaugh::rotlaugh::yes)


31905



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.........:devil2:

[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 :whistle:) '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 :whistle:) '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 :devil2:

[Paul_Hossler]

The American spelling is double dutch to me :devil2:

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