Replies

Thanks for your response Paul, I will check out your suggestions.

Regards
useful

Thank you Bundaburra, I’ll give it a try.

Regards
useful

Hi Zeddy

What can I say … you are a real genius! I am so grateful that you responded and did it so promptly!

I only wish I could return the favour!

Eternally grateful!

useful

Thank you all. I have managed to work it all out (and learned a lot in the process) so all of the issues are now resolved.

useful

I forgot to add that when I replace the number 1 with a tick, the colours disappear and as I can’t find where they are, I am unable to restore them.

I hope someone can assist.

Thank you in anticipation.

useful

I have managed to achieve two of the above three challenges, but I am unable to change the number 1 to a tick in the code, which for convenience, is listed below:

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim zCol
Dim zRow

zCol = Target.Column ‘column number of selected cell
zRow = Target.Row ‘row number of selected cell

If zRow = 1 Then Exit Sub ‘ignore cell selection in header row
If zRow > 456 Then Exit Sub ‘ignore cell selection past last row
If zCol [h1].Column Then Exit Sub ‘ignore columns to right of col [H]

‘target column must be in range [E:H]

If Target.Count 1 Then Exit Sub ‘ignore highlighting of multiple cells

‘only single cell selected or clicked, so..

If Target.Value = 1 Then ‘existing cell already has a 1 entered, so..
Target = “” ‘clear existing entry
Else ‘otherwise..
Target = 1 ‘set cell entry to 1
End If ‘end of test for existing entry

‘—————————————-
Select Case zCol

Case 5 ‘cell in col [E] clicked
Cells(zRow, 6) = “” ‘clear anything in [F]
Cells(zRow, 7) = “” ‘clear anything in [G]
Cells(zRow, 8) = “” ‘clear anything in [H]

Case 6 ‘cell in col [F] clicked
Cells(zRow, 5) = “” ‘clear anything in [E]
Cells(zRow, 7) = “” ‘clear anything in [G]
Cells(zRow, 8) = “” ‘clear anything in [H]

Case 7 ‘cell in col [G] clicked
Cells(zRow, 5) = “” ‘clear anything in [E]
Cells(zRow, 6) = “” ‘clear anything in [F]
Cells(zRow, 8) = “” ‘clear anything in [H]

Case 8 ‘cell in col [H] clicked
Cells(zRow, 5) = “” ‘clear anything in [E]
Cells(zRow, 6) = “” ‘clear anything in [F]
Cells(zRow, 7) = “” ‘clear anything in [G]

End Select

Calculate ‘<< require for recalculation of conditional formatting

End Sub 'Worksheet_SelectionChange

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)

Dim zCol

zCol = Target.Column 'column number of selected cell

If zCol [h1].Column Then Exit Sub ‘ignore columns to right of col [H]

‘target column must be in range [E:G]

If Target.Count 1 Then Exit Sub ‘ignore highlighting of multiple cells
Cancel = True

‘only single cell selected, so..

Target = “” ‘clear existing entry
Calculate ‘<< require for recalculation of conditional formatting

End Sub

I would be very grateful for your help.

Thank you in anticipation.

useful

Hi All

As you can clearly see, I’m just an keen amateur …

zeddy interpreted it all correctly, in spite of me. Clearly it makes no sense to be able to select more than one column in any one row. I should have made that clear. My apologies.

Whilst I can’t code, I can sort of read it, especially code as well commented as zeddy’s. Looking at it very carefully, I struggled to find anything relating to the colours in those cells that I copied from RG, or find the validation for the numbers, or the pick-list for the Y/N, but it all works fine. As a result, I was not surprised that when I copied the code and put it into my spreadsheet nothing worked.

Undeterred, I then tried to copy my spreadsheet into the one set up by zeddy … unfortunately that failed as well. Nothing copied and the whole process just ‘hung’.

In summary this is what I would like to achieve:

– Copy my spreadsheet into the one set up by zeddy, so that it all works;
– Change the number 1 into a centred tick, just as zeddy proposed;
– Fix the issue where clicking in the unprotected cell when the zeddy spreadsheet opens, does nothing.

Thank you in anticipation.

useful

I seem to have got excited a little too soon … the code worked like a charm at the beginning, but when I added worksheet protection, it failed and also, it adds a 1 to every cell I click on, not just in the columns I want it to.

A small test spreadsheet (with the VBA code) is attached here, so I can better explain what I am trying to achieve:

– The cells in grey are protected and the ones in white can be typed in (the password is test)
– The cells in Column C will have numbers typed in
– The cells in Column D will either have N or Y typed in
– The cells in Columns E, F, G and H are the only cells to have colours when clicked and the number 1 inserted, nowhere else

I would be very grateful if someone could help me with this.

Thank you in anticipation.

useful :confused:

Thank you all. You are wonderful!

My issue is resolved and I am very grateful that the knowledgeable responses were so prompt!

Long may you continue to give sound advice!

useful

Sorry, my shorthand is obviously too cryptic.

What I mean is, could you please add to the code, such that in addition to changing colour when clicked, the cell also now has a number 1 in it.

Thank you.

useful

Hi Amazing Geek!

You are a gem (as always). Your code worked an absolute treat!

The one thing I forgot in my previous post, was to also populate the clicked cell with 1 apart from the colour.

Thank you very much in anticipation.

useful

Thank you for the explanation Charles.

Regards
useful

Thank you folks!

I did not realise that Word would ‘remember’ a deleted second page in a template. Very clever.

Regards
useful

Thank you again Andrew.

Regards
useful

Hi Andrew

I forgot to ask you, could you please tell me where I can find a list of the icons, one of which, 526, you inserted into the code.

Thank you.

Regards
useful