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 do not understand this part of your question:
You said:
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".

What do you mean as highlighted.
Do you mean double clicked on or right clicked on or selected. Excel does not use the term "Highlighted"

 

[MJ72]

I do not understand this part of your question:
You said:
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".

What do you mean as highlighted.
Do you mean double clicked on or right clicked on or selected. Excel does not use the term "Highlighted"

Yes, sorry, you are correct. By “highlighted” I mean “double clicked on” therefore “filling” the cell yellow.

MJ

 

[My Aswer Is This]

In your original post I thought you said Column A B and C
But then you show

values in AC,AD or AE.

Is this column AC and column AD and AE

Try this script.
If you double click on any cell in column A or Column B or column C
The value will be added to column D

So if in A1 you double click on 5 the value 5 will be added to the cell same row column D
And the same with columns B and C

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Modified  10/4/2022  8:50:25 PM  EDT
Cancel = True
If Target.Column < 4 Then Cells(Target.Row, 4).Value = Cells(Target.Row, 4).Value + Target.Value
End Sub

If this works then I will get back with you on turning cell colors to yellow

 

[My Aswer Is This]

This new code adds the color to the cells double clicked on

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Modified  10/4/2022  8:59:54 PM  EDT
Cancel = True
If Target.Column < 4 Then
Cells(Target.Row, 4).Value = Cells(Target.Row, 4).Value + Target.Value
Target.Interior.ColorIndex = 6
End If
End Sub

 

[MJ72]

In your original post I thought you said Column A B and C
But then you show

values in AC,AD or AE.

Is this column AC and column AD and AE

Try this script.
If you double click on any cell in column A or Column B or column C
The value will be added to column D

So if in A1 you double click on 5 the value 5 will be added to the cell same row column D
And the same with columns B and C

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Modified  10/4/2022  8:50:25 PM  EDT
Cancel = True
If Target.Column < 4 Then Cells(Target.Row, 4).Value = Cells(Target.Row, 4).Value + Target.Value
End Sub

If this works then I will get back with you on turning cell colors to yellow

Good morning! Thank you very much for trying to help, unfortunately when I replaced the Worksheet code with yours, it simply transferred the contents of the first cell into the cell beside it.
I'm afraid I maybe haven't explained myself correctly. I have a page that needs to score responses (kind of like a personality test). Reponses to a certain task are kept in Column A, Column B, and Column C, with each column being worth a certain number of points when double clicked. A=5, B=3 and C=1. Column D is for "Total points". (yes, it is possible to select all three responses and get a total of 9 points.

I have a worksheet code already in place that allows me to double click a cell and the cell then becomes "filled" in yellow or right click a cell and have the fill colour removed. (see below for code)

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 now is to attribute the respective point values to the cells in each column (A=5, B=3, C=1) and have the total number of points of the cells double clicked in each row reflect in column D.
The reason I mentioned AC, AD and AE is that I thought I had a solution by having an IF statement reflect those point values in another row of cells so that I could simply SUM the total and have it reflect back in Column D. (See statement for Column AC (reflecting Column A points) below)

=IF(ColorIndex($C$11)=6,"5","0")

Unfortunately, this doesn't update automatically when the cells in Column A are double clicked or right clicked. I have to actually select the statement in the formula bar and press enter before AC will update. I need the totals to update automatically.

If it helps here is what the first row of columns A,B,C and D look like to the user:

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

 

[My Aswer Is This]

You said:
Good morning! Thank you very much for trying to help, unfortunately when I replaced the Worksheet code with yours, it simply transferred the contents of the first cell into the cell beside it.

If you double click on any cell in column A B Or C that value is added to column 4
Not sure why your saying :
it simply transferred the contents of the first cell into the cell beside it.

 

[MJ72]

You said:
Good morning! Thank you very much for trying to help, unfortunately when I replaced the Worksheet code with yours, it simply transferred the contents of the first cell into the cell beside it.

If you double click on any cell in column A B Or C that value is added to column 4
Not sure why your saying :
it simply transferred the contents of the first cell into the cell beside it.

Because that’s what it did in my worksheet. When I double clicked, it copied the text from the cell in column A and pasted it into column B in front of the text that was already there. Nothing populated into Column D at all. Sorry.

 

[My Aswer Is This]

You said:
it copied the text from the cell

I thought from your posting there are numbers in column A B and C and the sum of the values go into column D

Here you said:
A=5, B=3 and C=1. Column D is for "Total points". (yes, it is possible to select all three responses and get a total of 9 points.

Show me the code you are using.

 

[MJ72]

You said:
it copied the text from the cell

I thought from your posting there are numbers in column A B and C and the sum of the values go into column D

Here you said:
A=5, B=3 and C=1. Column D is for "Total points". (yes, it is possible to select all three responses and get a total of 9 points.

Show me the code you are using.

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.