Logic Behind Highlighting Ranges Based on Selection

[Mousehunter]

Hello there!

I am trying to connect the dots here between calculation event, selection change event and update of conditional formatting...

I know that if I want to highlight the column and the row of a selected cell, I must select a range and conditionally format it with this rule: =+OR(CELL("col")=COLUMN();CELL("row")=ROW()).

I also know that if I do not force recalculation of the sheet, I will not get the desired event because the formula in the rule of conditional formatting will not recalculate and the formatting will not be applied to the row and column of the newly selected cell.

This means that I must force recalculation with every selection change event. But by doing so, I also know that I empty the clipboard and I am restricting myself from pasting a copied or cut range. To enable me to paste a cut or copied range, I must block the recalculation event if the clipboard is not empty. I do this with the following code.

But by doing this I am blocking the recalculation which is a prerequisite for the conditional formatting to work. A vicius cirlce, an infinite logic loop.

A vicius cirlce, an infinite logic loop!

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Application.CutCopyMode = 0 Then
Target.Calculate '    Application.Calculate
End If

End Sub

But it seems to work! How can this be? I have blocked recalculation and after pasting the clipboard is still not empty. How can this be working?

Any ideas?

 

[Yongle]

Add another event

Private Sub Worksheet_Change(ByVal Target As Range)
    Select Case Left(Application.CommandBars("Standard").Controls("&Undo").List(1), 5)
        Case "Paste": Application.CutCopyMode = 0
    End Select
End Sub

After pasting the clipboard is still not empty. How can this be working?

The clipboard does not empty automatically after pasting

 

[Mousehunter]

Perhaps you have not understood the question because I was writing too many things.

The question is: How can something that works only with calculation be working when we tell it not to calculate?

I know the clipboard does not empty after pasting. I am wondering cow can this be working with a non empty clipboard. I have commanded it not to calculate and yet it calculates. Unless there is another event after selection change which is the paste event and this triggers calculation.

PS: Thanks for exposing me to the CommandBars object. I will study to understand your code which seems to be doing the same

 

[Yongle]

This means that I must force recalculation with every selection change event. But by doing so, I also know that I empty the clipboard and I am restricting myself from pasting a copied or cut range. To enable me to paste a cut or copied range, I must block the recalculation event if the clipboard is not empty. I do this with the following code.

Is your statement correct ? That is NOT what I observe with Excel 365

I used tyour CF rule and ONLY this code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Target.Calculate
End Sub

I can cut, copy and paste without any problem
The clipboard does not empty
The activecell row and column are highlighted

 

[Mousehunter]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Target.Calculate
End Sub

I can cut, copy and paste without any problem
The clipboard does not empty
The activecell row and column are highlighted

I tried myself this one, it worked once on my pc but after some trials it did not work so I reverted to the code I initially posted.

All in all, I think that that Excel calculates twice. Once when we change the selection and another time when we paste the value. I have not found a way to monitor how many times it calculates, but I am pasting the Target value on a cell and I see different value than the one I see on the target cell itself.

I use a randbetween function in a range let's say 1 to 1.000. See for yourself below. [a14] is equal to 720 where it should have a value of 597 because this is the target of the selection change.

Try it for yourself. you will find it to be true...

I use the code below

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'If Application.CutCopyMode = 0 Then
Target.Calculate '    Application.Calculate
'End If

[a14] = Target
End Sub

 

[Fluff]

Because randbetween is a volatile function, it will calculate when any cell on the sheet is changed.
Therefore when you change the value of A14 the formulae will calculate again.