Further news...... Ok, the scoring is now working but there is an
exception to this.
Ultimately what I need solving is..........
There are questions on the sheet with more than one set of options,
how can I get the lowest score from both sets of options to populate
the results field?? e.g.....
0 1 2 Result
option1 option2 option3
Q1 option1 option2 option3
option1 option2 option3
Each row of options will be scored from 0-2, however the Result column
needs to show the lowest score from all the options.
Say we score row 1 as option 2, row 2 as option 1 and row 3 as option
3 then row 2 and a score of zero will be in the Results column.
Your help with this is greatly appreciated
On Apr 16, 11:07 am, steviewood <steven.w...@ctcplc.com> wrote:
> 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.- Hide quoted
> > text -
>
> - Show quoted text -
--~--~---------~--~----~------------~-------~--~----~
-------------------------------------------------------------------------------------
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
-------------------------------------------------------------------------------------
-~----------~----~----~----~------~----~------~--~---
