How to automatically assign a value to a cell based on the colour of another.

[MJ72]

Hello all!
Hopefully you can help with this. I am working on a form that needs to be interactive for the user. It has three columns of possible answers to certain questions. So far I have been able to code the form to highlight in yellow (ColorIndex=6) the selected answer based on a double click to highlight and right click to remove highlight.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
Target.Interior.ColorIndex = 6
End Sub

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
Target.Interior.ColorIndex = 0
End Sub

What I need to do now is assign a point value to the answers so that the points can be tallied in a fourth column.

For example if the answers in column A, B, C are worth 5pts, 3pts and 1pt respectively then when either A,B and/or C is highlighted, the total value in column D would be "5" or "3" or "1" or if all three are selected then the point total in column D would obviously be "9".

I've gotten this far by using columns over to the right (columns AC,AD,AE) in my work sheet:

=IF(ColorIndex($A$1)=6,"5","0")
=IF(ColorIndex($A$2)=6,"3","0")
=IF(ColorIndex($A$3)=6,"1","0")

Which if this worked the way it was supposed to, I could then simply SUM up the rows and there you have it. My problem is that when I highlight or "de-highlight" cells, it does not automatically update the values in AC,AD or AE.
I actually have to click on the formula in these cells individually and press enter before they'll update.

How do I get this to function automatically?

Thanks

MJ

 

[My Aswer Is This]

I want you to enter a 9 in Range

I did in my previous messages. I don’t have a code for assigning a point value for each cell. I initially just used the IF statement that I posted earlier to attribute point values and reflect them in columns AC, AD and AE but as I mentioned it would only update the value if I selected the statement in the formula bar and pressed enter again. I need it to do that automatically…without having to scroll over each time to update.

Someone else will need to help you as I'm confused. I thought we wanted a code where we double click on number values in columns A B and C and want the sum of these values in column D

 

[MJ72]

I want you to enter a 9 in Range

Someone else will need to help you as I'm confused. I thought we wanted a code where we double click on number values in columns A B and C and want the sum of these values in column D

I completely understand…however let me try to explain it once more. Please look at the columns I included in a previous message. So far when I double click a cell (that is populated with possible answers to questions) in columns A,B or C it turns yellow. What I need to happen is not only turn yellow but also put it’s respective score in column D. If multiple answers are selected then the score obviously needs to update accordingly. Have I explained that correctly?

 

[Eric W]

Give this code a shot:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim s As Long

    If Intersect(Target, Range("A:C")) Is Nothing Then Exit Sub
    Cancel = True
    
    Target.Interior.ColorIndex = -4136 - Target.Interior.ColorIndex
    s = 0
    If Cells(Target.Row, "A").Interior.ColorIndex = 6 Then s = s + 5
    If Cells(Target.Row, "B").Interior.ColorIndex = 6 Then s = s + 3
    If Cells(Target.Row, "C").Interior.ColorIndex = 6 Then s = s + 1
    Cells(Target.Row, "D") = s
    
End Sub

Also note that double clicking a cell in columns A:C will toggle the yellow highlighting, so you don't need the right click code.

 

[MJ72]

Give this code a shot:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim s As Long

    If Intersect(Target, Range("A:C")) Is Nothing Then Exit Sub
    Cancel = True
   
    Target.Interior.ColorIndex = -4136 - Target.Interior.ColorIndex
    s = 0
    If Cells(Target.Row, "A").Interior.ColorIndex = 6 Then s = s + 5
    If Cells(Target.Row, "B").Interior.ColorIndex = 6 Then s = s + 3
    If Cells(Target.Row, "C").Interior.ColorIndex = 6 Then s = s + 1
    Cells(Target.Row, "D") = s
   
End Sub

Also note that double clicking a cell in columns A:C will toggle the yellow highlighting, so you don't need the right click code.

Logically, it looks like it should work, thank you. I’ll try it when I get back to the office tomorrow morning. Thanks again, Have a good night!

MJ

 

[MJ72]

Give this code a shot:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim s As Long

    If Intersect(Target, Range("A:C")) Is Nothing Then Exit Sub
    Cancel = True
   
    Target.Interior.ColorIndex = -4136 - Target.Interior.ColorIndex
    s = 0
    If Cells(Target.Row, "A").Interior.ColorIndex = 6 Then s = s + 5
    If Cells(Target.Row, "B").Interior.ColorIndex = 6 Then s = s + 3
    If Cells(Target.Row, "C").Interior.ColorIndex = 6 Then s = s + 1
    Cells(Target.Row, "D") = s
   
End Sub

Also note that double clicking a cell in columns A:C will toggle the yellow highlighting, so you don't need the right click code.

Good Morning Eric,

Thank you again, however, your code, when I clicked on column A replace the text in column B with a 1. The cell also did not turn yellow. (see below)

Before:

Criteria	5	3	1	Total
Educational Background	High school not completed	High school graduate OR internationally Trained OR Registered Apprentice	College/ University education not completed

After:

Criteria	5	3	1	Total
Educational Background	High school not completed	1​	College/ University education not completed

What I am aiming for:

Critère	5	3	1	Total
Educational Background (yellow fill does not copy/paste)	High school not completed	High school graduate OR internationally Trained OR Registered Apprentice	College/ University education not completed	5

Or if multiple answers are clicked it would total the scores and put that number in column D ("Total")

 

[My Aswer Is This]

I'm still watching but see you still do not have a solution.
You continue to say clicked but I assume you mean doubleclick quickly

Your quote:
For example if the answers in column A, B, C are worth 5pts, 3pts and 1pt respectively then when either A,B and/or C is highlighted, the total value in column D would be "5" or "3" or "1" or if all three are selected then the point total in column D would obviously be "9".

So how does the script know which answer is worth how many points
So if I double click on columns A B and C what should the value in column D be ?

 

[MJ72]

I'm still watching but see you still do not have a solution.
You continue to say clicked but I assume you mean doubleclick quickly

Your quote:
For example if the answers in column A, B, C are worth 5pts, 3pts and 1pt respectively then when either A,B and/or C is highlighted, the total value in column D would be "5" or "3" or "1" or if all three are selected then the point total in column D would obviously be "9".

So how does the script know which answer is worth how many points
So if I double click on columns A B and C what should the value in column D be ?

If you were to "double-click" on all three columns the total would be 9 points in column D. If you were only to double-click on A it would be 5points in D, B=3 points and C=1point.

 

[My Aswer Is This]

Try this:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Modified  10/6/2022  5:03:21 PM  EDT
Cancel = True
Dim ans As Long

If Target.Column < 4 Then
ans = Target.Row

    Select Case Target.Column

        Case 1
            Cells(ans, 4).Value = Cells(ans, 4).Value + 5

        Case 2
            Cells(ans, 4).Value = Cells(ans, 4).Value + 3

        Case 3
            Cells(ans, 4).Value = Cells(ans, 4).Value + 1
    End Select
End If

End Sub

 

[Eric W]

First, I think we have a big disconnect when you say columns A, B, and C. By your sample, I think you mean B, C, D, with the total going in column E, like so:

Book1
	A	B	C	D	E
1	Criteria	5	3	1	Total
2	Educational Background	High school not completed	High school graduate OR internationally Trained OR Registered Apprentice	College/University education not completed	0
Sheet3

That explains why you're getting the 1 overwriting another cell, because I didn't understand where you really wanted it.

Second, it might not have highlighted the cell because the technique I used to toggle the color depends on there being no fill of any other color in it first. If that's not the case, it might not have worked right. I changed to a slightly longer, but more robust method.

Here's the updated macro:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim s As Long

    If Intersect(Target, Range("B:D")) Is Nothing Then Exit Sub
    Cancel = True
    
    If Target.Interior.ColorIndex = 6 Then
        Target.Interior.Pattern = xlNone
    Else
        Target.Interior.ColorIndex = 6
    End If
    
    s = 0
    If Cells(Target.Row, "B").Interior.ColorIndex = 6 Then s = s + 5
    If Cells(Target.Row, "C").Interior.ColorIndex = 6 Then s = s + 3
    If Cells(Target.Row, "D").Interior.ColorIndex = 6 Then s = s + 1
    Cells(Target.Row, "E") = s
    
End Sub

Now, when I double-click on the B2 cell, this is what happens:

Book1
	A	B	C	D	E
1	Criteria	5	3	1	Total
2	Educational Background	High school not completed	High school graduate OR internationally Trained OR Registered Apprentice	College/University education not completed	5
Sheet3

Is this what you want?

 

[My Aswer Is This]

My script does not change the color of the cell.
I would like to get the results we want in column D
Then have you explain the thing about the cells color.
And from your last post we are dealing with columns A B C and D
Column D gets the final result