Logic Behind Highlighting Ranges Based on Selection

Mousehunter

New Member
Joined
May 6, 2008
Messages
13
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
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!

VBA Code:
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?
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
Add another event
VBA Code:
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

New Member
Joined
May 6, 2008
Messages
13
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
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

VBA 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

New Member
Joined
May 6, 2008
Messages
13
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
VBA 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

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

VBA Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

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

[a14] = Target
End Sub


1592743669528.png
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,570
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,613
Messages
5,625,848
Members
416,139
Latest member
MattBoard

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
Top