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

MJ72

Board Regular
Joined
Aug 17, 2021
Messages
64
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
 
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
 
Upvote 0

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
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?
 
Upvote 0
Give this code a shot:

VBA Code:
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.
 
Upvote 0
Give this code a shot:

VBA Code:
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
 
Upvote 0
Give this code a shot:

VBA Code:
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:
Criteria531Total
Educational BackgroundHigh school not completedHigh school graduate OR internationally Trained OR Registered ApprenticeCollege/ University education not completed

After:
Criteria531Total
Educational BackgroundHigh school not completed
1​
College/ University education not completed

What I am aiming for:
Critère531Total
Educational Background
(yellow fill does not copy/paste)
High school not completedHigh school graduate OR internationally Trained OR Registered ApprenticeCollege/ University education not completed5

Or if multiple answers are clicked it would total the scores and put that number in column D ("Total")
 
Upvote 0
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 ?
 
Upvote 0
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.
 
Upvote 0
Try this:
VBA Code:
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
 
Upvote 0
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
ABCDE
1Criteria531Total
2Educational BackgroundHigh school not completedHigh school graduate OR internationally Trained OR Registered ApprenticeCollege/University education not completed0
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:

VBA Code:
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
ABCDE
1Criteria531Total
2Educational BackgroundHigh school not completedHigh school graduate OR internationally Trained OR Registered ApprenticeCollege/University education not completed5
Sheet3


Is this what you want?
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

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