Ok scrub this I have got it working now for all cells, except for the
part that updates the number in column I. So depending on the column
selected it needs to go to the row with the score on to select the
score.
Please see new macro,
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel
As Boolean)
On Error GoTo endit
ActiveSheet.Unprotect Password:=Sheets("ChartMappings").Range
("AA1").Value
Dim myRange As String
Dim clearcell As String
myRange = Target.Address
clearcell = Split(myRange, "$")(2)
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(myRange)) Is Nothing Then
With Target
If .Interior.ColorIndex = 45 Then
Range("D" & clearcell & ":" & "H" &
clearcell).Interior.ColorIndex = xlNone
.Interior.ColorIndex = xlNone
Else
Range("D" & clearcell & ":" & "H" &
clearcell).Interior.ColorIndex = xlNone
.Interior.ColorIndex = 45
End If
End With
Cancel = True
End If
endit:
Application.EnableEvents = True
ActiveSheet.Protect Password:=Sheets("ChartMappings").Range
("AA1").Value, DrawingObjects:=True
End Sub
Thanks.
On Apr 16, 9:19 am, steviewood <steven.w...@ctcplc.com> wrote:
> Hi,
>
> I have a worksheet which operates as a questionnaire with scoring
> cloumns. I have managed to write a macro that will change the colour
> of the cell upon double clicking however I'd also like to do a few
> more things that with my VB knowledge I'm unable to code.
>
> What I'd like to happen is,
>
> - select any row with an even row number in column D (currently
> hardcoded to D16 in macro below)
> - add the score from a pre-defined cell to a cell on the same row in
> the results column
> - ensure the colour of the other columns selectable is 'no fill' where
> not equal to the column selected
> - ensure that if another column is selected the colour and score
> change to correspond with the new selections (looks like it will just
> reverse the macro below and run the macro for the next column accross)
>
> e.g. - currently when Option1 is double clicked the cell containing
> option1 will turn red and when double clicked again will go to 'no
> fill'. Ideal result would for it to handle the other options as well
> and populate the result cell with the score from above each option.
>
> 0 1 2 Result
> Q1 option1 option2 option3 0
> Q2 option1 option2 option3 0
>
> the macro I'm using is as follows, obviously there will need to be
> more than one macro for each column unless variables can be applied to
> all the ranges???.......
>
> Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel
> As Boolean)
> Const myRange As String = "D16"
> On Error GoTo endit
> Application.EnableEvents = False
> If Not Intersect(Target, Me.Range(myRange)) Is Nothing Then
> With Target
> If .Interior.ColorIndex = 3 Then
> .Interior.ColorIndex = xlNone
> Else
> .Interior.ColorIndex = 3
> End If
> End With
> Cancel = True
> End If
> endit:
> Application.EnableEvents = True
> End Sub
>
> Any help would be most appreciated as I'm rubbish with VB.
--~--~---------~--~----~------------~-------~--~----~
-------------------------------------------------------------------------------------
Some important links for excel users:
1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at
http://www.excelitems.com
2. Excel tutorials at http://www.excel-macros.blogspot.com
3. Learn VBA Macros at http://www.vbamacros.blogspot.com
4. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
If you find any spam message in the group, please send an email to:
Ayush Jain @ jainayus...@gmail.com or
Ashish Jain @ 26may.1...@gmail.com
-------------------------------------------------------------------------------------
-~----------~----~----~----~------~----~------~--~---
