Help with vba code: highlighting by clicking on cell XL13 Win 7

Corb5a

New Member
Joined
Jun 10, 2015
Messages
3
I'm am part of a charitable initiative helping people with disabilities / life challenges get into work. Myself a colleague are trying to put an interactive excel calendar together so that they identify the days that they can work, which we help them calculate earnings ect.

I have no issue with creating a calendar but we have been trying to use vba code to create a calendar whereby they can highlight days by clicking on them and unhighlight them by clicking on the day again.

This code seems to pretty much do the job:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
Select Case Target.Interior.ColorIndex
Case xlNone, 4: Target.Interior.ColorIndex = 3
Case Else: Target.Interior.ColorIndex = 4
End Select
End Sub

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
Target.Interior.ColorIndex = xlNone
End Sub
The issue is we would need this to affect only one sheet of the workbook, use only one color, and a range of cells (i.e. not the whole sheet, for example A:3 to G:6. It's driving us mad that we can't figure this out because we're so new to this!

It would be cool and helpful if every time they selected/deselected a day it also created a value for the total number of days that they can work somewhere on the spreadsheet, but I realise this might be too difficult to achieve.

Any help would be greatly appreciated.

Thanks.
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Try this:
Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("A3:G6")) Is Nothing Then

Cancel = True
 Select Case Target.Interior.ColorIndex
 Case xlNone, 4: Target.Interior.ColorIndex = 3
 Case Else: Target.Interior.ColorIndex = 4
 End Select
 End If
 End Sub

 Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
 If Not Intersect(Target, Range("A3:G6")) Is Nothing Then
Cancel = True
 Target.Interior.ColorIndex = xlNone
End If
End Sub
 
Upvote 0
As far as this request:
It would be cool and helpful if every time they selected/deselected a day it also created a value for the total number of days that they can work somewhere on the spreadsheet, but I realise this might be too difficult to achieve.

I need to know exactly where you want this put this on the sheet. If they double click a cell it adds 1 to cell A1 value for example and if they right click a cell it reduces the number in A1 is that what you want?
 
Upvote 0
Ok. I have included your request for a counter in this script. It puts the count in cells”A1”.
If you want to change it just do so. You will notice this in both parts of the script the double click and the right click.
You will notice this entire script only applies to range “A3:G6” as per your request.
You can notice this in the top part of the script and change it if you wish.
Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("A3:G6")) Is Nothing Then

Cancel = True
 Select Case Target.Interior.ColorIndex
 Case xlNone, 4: Target.Interior.ColorIndex = 3
 Case Else: Target.Interior.ColorIndex = 4
 End Select
 [A1].Value = [A1].Value + 1
 End If
 End Sub

 Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
 If Not Intersect(Target, Range("A3:G6")) Is Nothing Then
Cancel = True
 Target.Interior.ColorIndex = xlNone
[A1].Value = [A1].Value - 1

End If
End Sub
 
Upvote 0
thank you so much for all your help! Sorry I haven't had a chance to put this into practice yet. I will try it out tomorrow but it sounds perfect. I'll report back.

Thanks again!
 
Upvote 0
Thanks for your comments. It's good to hear back from Mr.Excel users.
thank you so much for all your help! Sorry I haven't had a chance to put this into practice yet. I will try it out tomorrow but it sounds perfect. I'll report back.

Thanks again!
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,144
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top