# Whist score sheet frustration

#### ADR2015

##### New Member
Hi. during lockdown we have been playing progressive whist online for nearly a year now. I've decided to record scores on a spreadsheet so all can see on zoom. i have managed to do a basic formula below, however there are some tweaks to it that i can't seem to resolve. Probably really simple but i am a beginner here. i have three columns basically, Bid, won and score. lets say columns A, B and C. Column A will contain the bid, values 0 to 7. B will show what they actually achieved, values 0 to 7 also. if the values match, column C should show a sum of 10 plus 2 points for every trick, i.e. call 2 win 2 points are 10+(2*2)=14 points. this needs to then be added to the value above in c to give running score. My formula does that, however if I add a 0 to a cell in A, and the adjacent cell in B is blank, the number 10 appears in the C column. i need to tell it to only do the calculation if both of the cells A and B have a number in it. if the two scores dont match then only the running score above is entered. Also if i dont want any figure at all to appear in the c column until the calculation is done, how do i show that.
The formula i did is =IF(L8=K8,(M7+10+(2*K8)),M7) assuming the cells im using are L, K and M

thanks

alan

### Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

#### Toadstool

##### Well-known Member
Hi ADR2015,

It was a little tricky to follow but is this what you seek?

ADR2015.xlsx
KLM
1BidWonScore
2
310
410
52214
614
7214
8214
92228
1028
Sheet1
Cell Formulas
RangeFormula
M3:M10M3=IF(AND(ISNUMBER(K3),ISNUMBER(L3),L3=K3),(M2+10+(2*K3)),M2)

#### ADR2015

##### New Member
Hi ADR2015,

It was a little tricky to follow but is this what you seek?

ADR2015.xlsx
KLM
1BidWonScore
2
310
410
52214
614
7214
8214
92228
1028
Sheet1
Cell Formulas
RangeFormula
M3:M10M3=IF(AND(ISNUMBER(K3),ISNUMBER(L3),L3=K3),(M2+10+(2*K3)),M2)
Hi Toadstool

Thanks for the quick reply. Works perfectly!!!!! just with a slight wrinkle. sorry if table not as neat as yours. in the score column, how can you prevent any return (i.e. cell to remain blank) until both of the other cells have been completed? is that possible? just housekeeping really. thank you very much

 ADR2015 Cards Call Won Score 1​ 0 0 10 2​ 0 0 20 3​ 1 1 32 4​ 0 0 42 5​ 1 2 42 6​ 1 2 42 7​ 1 0 42 bc 4 4 60 mis 0 0 80 nt 0 2 80 7​ 80 6​ 80 5​ 80 4​ 80 3​ 80 2​ 80 1​ 80 80

#### Toadstool

##### Well-known Member
It's been over 50 years since I played Whist so I'm not sure if this is correct. Please take a lok:

ADR2015.xlsx
JKLM
1CardsBidWonScore
21000
32000
431112
540012
651212
761212
871012
9bc4430
10mis0030
11nt0230
127
136
145
154
163
172
181
Sheet3
Cell Formulas
RangeFormula
M2:M18M2=IF(OR(K2="",L2=""),"",IF(AND(ISNUMBER(K2),ISNUMBER(L2),L2=K2,K2>0),(MAX(\$M\$1:M1)+10+(2*K2)),MAX(\$M\$1:M1)))

#### ADR2015

##### New Member

ADVERTISEMENT

Excellent, thanks. your second formula wasn't quite right, but i used your previous one and simply added the section =IF(OR(K2="",L2=""),"", to get the blanks where no scores have been added. This then works perfectly. thank you so much!!

As you are really helping, there is one more I would like to add. someone suggested it would be easier if the cell i was working on (i.e waiting for their call) would be highlighted. I used the VBA as follows; (their suggestion)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.ScreenUpdating = False
Cells.Interior.ColorIndex = 0
Target.Interior.Color = vbCyan
Application.ScreenUpdating = True
End Sub

this does indeed highlights the selected cell cyan, but has the detrimental effect of removing all other colours on my spreadsheet. how can i modify this so that only the cell i am on at the moment is highlighted, but leaves all other colours alone, and then returns to no-fill colour when moves off it?

sorry if i have asked too many questions.

thanks.

#### Toadstool

##### Well-known Member
I'm not a VBA expert but I believe Cells.Interior.ColorIndex = 0 is what's removing the other colors.

#### Eric W

##### MrExcel MVP

ADVERTISEMENT

Try this for your Selection_Change highlighting:

VBA Code:
``````Public LastCell
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
LastCell.Interior.ColorIndex = 0
Target.Interior.Color = vbCyan
Set LastCell = Target
End Sub``````

Last edited:

#### ADR2015

##### New Member
Try this for your Selection_Change highlighting:

VBA Code:
``````Public LastCell
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
LastCell.Interior.ColorIndex = 0
Target.Interior.Color = vbCyan
Set LastCell = Target
End Sub``````
Many thanks Eric. works fine, however doesn't work when the sheet is protected, even though the cells i access are unprotected.

#### Eric W

##### MrExcel MVP
That seems like a bit of an odd choice for Microsoft, to not allow formatting of unprotected cells. But when you protect the sheet, you can check the "Format cells" box and the macro should work. Otherwise, we'd need to add some Unprotect/Protect lines to the macro.

#### ADR2015

##### New Member
That seems like a bit of an odd choice for Microsoft, to not allow formatting of unprotected cells. But when you protect the sheet, you can check the "Format cells" box and the macro should work. Otherwise, we'd need to add some Unprotect/Protect lines to the macro.
Works perfectly. Thanks a million.... wish I had your knowledge....

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Threads
1,164,632
Messages
5,838,474
Members
430,549
Latest member
jayjay2022

### 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

### 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