I need to fix a bug in some excel VBA code

Hi there ... I have an issue with a spreadsheet that I have built. It has a reasonably large amount of VBA code that I wrote myself after taking a short VBA course. However, there is a problem that I am having.

We have a formula that requires one input in one cell and gives an output in another cell. For example, it may be that a "volatility" of 25.00% in the input cell could return a "premium" of 1.02 in the output cell. However, we also sometimes reverse the formula and may want to know what "volatility" would be required for a particular "premium" - so we may want to INPUT 1.05 lets say and get an OUTPUT of 27.50% (just making up numbers here), or obviously we may want to confirm that an input of 1.02 returns an output of 25.00% as in the first example but the other way around.

For this reason we have an "input indicator" cell that is "v" if we want to put a "volatility" in the input cell and have a "premium" output in the output cell, or, a "p" in the indicator cell reverses the formula so that a "premium" can be entered into the input cell and the "volatility" will be output.

This was all fine until we got to the problem of formatting when I realised that I want the input cell to be formatted as 0.00% and the output cell to be formatted as 0.000 when the indicator cell is "v" and the input cell 0.000, output 0.00% when the indicator is "p".

I wrote the code below in order to do this and it was all working very well. I would like to add that I didnt go down the route of using excel's built in "Conditional Formatting" because when you do that you cannot type "25+Enter" to input "25.00%" ... for some reason you have to enter it in the numerical format for 25.00% which is 0.25+Enter. This may seem trivial but we work on a trading floor and we need this to be as quick as possible. The problem we are having is that because of something to do with this code, if I copy a cell or multiple cells and then paste them somewhere else the clipboard stops working after one paste ... i.e. if I ctrl+c and then ctrl+v it pastes, but then if i go somewhere else and ctrl+v it seems the clipboard has been wiped and I need to go back and ctrl+c the original cell(s). Can you tell me what in that code is causing this?

Note: There are 2 columns of these formulas being calculated





Option Explicit 'All variables are declared explicitly.
Option Compare Text 'Uppercase letters to be equivalent to lowercase letters.
Option Base 1 'Specifies 0 or 1 as the default first index of arrays.

Private Sub Worksheet_Change(ByVal Target As Range)

'Turn off Excel functionality to improve performance
Application.ScreenUpdating = False
Application.Calculation = xlManual

'The variable KeyCells contains the cells that will cause an alert when they are changed
Dim KeyCells As Range
Set KeyCells = Range("N3:N102")

'Run code when one of the designated cells has been changed
If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then

If Target.Value = "v" Then
Cells(Target.Row, Target.Column + 2).NumberFormat = "#,##0.000"
Cells(Target.Row, Target.Column + 3).NumberFormat = "0.00%"
ElseIf Target.Value = "p" Then
Cells(Target.Row, Target.Column + 2).NumberFormat = "0.00%"
Cells(Target.Row, Target.Column + 3).NumberFormat = "#,##0.000"
End If

End If


'Turn Excel functionality back on
Application.ScreenUpdating = True
Application.Calculation = xlAutomatic

End Sub