Restrict highlighting of rows and columns to selected range

Shodi

Board Regular
Joined
May 24, 2016
Messages
53
Hi Everyone,

I have a macro-enabled worksheet in which I have used "=OR(CELL("col")=COLUMN(),CELL("row")=ROW())" in the Conditional Formating tool to highlight the row and column whenever a cell is selected in columns B:I and I have the below given VBA code applied...

VBA Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.ScreenUpdating = False

r = ActiveCell.Row
c = ActiveCell.Column


If c > 9 Then
Application.ScreenUpdating = True
Exit Sub
End If

If Application.CutCopyMode = False Then
Application.Calculate
End If
.....................
.......................

My problem is that following the above-given code if events happen in columns "B : I" there is a transpose procedure happening in columns BA and FA.
Right now what is happening is whenever the transpose procedure takes place the highlighting flickers up and down (the Application.ScreenUpdating is set to False so it does not flicker between columns) and is very irritating
Is it possible to make it so that the highlighting does not change if I select a column outside range B:I?

Meaning if I have selected cell C10, row B10 to I10 and Column C will be highlighted and if I select cell L5 or any other cell outside of columns B:I, highlighting of row B10 to I10 and Column C should not change till I make a selection in range B:I.

It will be very helpful if this can be done.

Thanks everyone.
 
Last edited by a moderator:

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Insert as first line of macro
VBA Code:
If Intersect(Range("B:I"), Target) Is Nothing Then Exit Sub
 
Upvote 0
Solution
Thanks a lot Yongle....It works...
Emm.. Guess I was hasty...sorry Now the selection does not change when a cell outside columns B:I is selected by when my code clears the transposed data in columns BA and FA row 1 gets highlighted.... can we fix this
 
Upvote 0
You said you wanted highlighting triggered when any cell in columns B:I selected
- which is dealt with

I think you also want the highlighting triggered when you run your other code
- is highlighting to be triggered every time the code is run?
- if not please detail exact requirements

Please post the other code
thanks
 
Upvote 0
You said you wanted highlighting triggered when any cell in columns B:I selected
- which is dealt with

I think you also want the highlighting triggered when you run your other code
- is highlighting to be triggered every time the code is run?
- if not please detail exact requirements

Please post the other code
thanks

Actually, I only want the highlighting triggered when I manually select a cell in range B:I, not when a code runs.
Your line of code helped to ensure that the selection_change event is not triggered outside of range B:I unless an editing function takes places - like delete or F2.
I think I managed to get around it.. I was using "Range("BA:GZ").ClearContents" to delete data that was transposed there. I changed that to Range("BA:GZ").ClearContents.Value = "". Now the highlighting does not change when the code runs.
Thanks for the assist.
 
Upvote 0
I have never seen this construction before. Glad it achieves what you require.
VBA Code:
Range("BA:GZ").ClearContents.Value
 
Upvote 0
I have never seen this construction before. Glad it achieves what you require.
VBA Code:
Range("BA:GZ").ClearContents.Value
Sorry Yongle, that was a typo... I meant
VBA Code:
Range("BA:GZ").Value = ""
[/QUOTE]
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,194
Members
448,554
Latest member
Gleisner2

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
Back
Top