Change colour in given range when any cell is selected remotely.

I'm hoping someone can offer some VBA advice for something I'm trying to do in my worksheet.

Essentially , if any cell in the range A3:O27 is selected , then cells in the range C1:O1 would be coloured green. Where no cell is selected in A3:O27 is selected then C1:O1 would have no colour.

Can someone suggest some VBA to achieve this? I was trying with CF but couldn't find a way.

Grateful for any help.

Like this:

In worksheet module:

PHP Code: 

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Range("C1:O1").Interior
    If Intersect(Target, Range("A3:O27")) Is Nothing Then
        .Color = xlNone
    Else
        .Color = vbGreen
    End If
End With
End Sub 


Many thanks.

It successfully colours C1:O1 green when any cell in A3:O27 is selected.

Unfortunately , when a cell outside A3:O27 is selected it doesn't change C1:O1 back to it's previous state. It leaves C1:O1 coloured lilac , and without gridlines.

Can you suggest an amendment to toggle it between green and it's original state?

I'll attach a file to show what I mean.

I tried to copy original color to other cell, i.e, S1. then use S1 cell as source cell.
The using this:

PHP Code: 

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Range("C1:O1").Interior
    If Intersect(Target, Range("A3:O27")) Is Nothing Then
        .Color = Range("S1").Interior.Color
    Else
        .Color = vbGreen
    End If
End With
End Sub 


Ok thanks.

This does reinstate the automatic colour to C1:O1 , but not the gridlines. So the gridlines are blank.

I wonder if there is a way to copy the formatting from S1 also.

I ran it on sheet2 of my sample so you can see the result attached.

Thanks. This does resolve the issue with the gridlines and successfully toggles between green and the previous format in c1:o1.

For some reason it's not triggering the colour when the cell in the range is selected via a link. It works fine when the cell is selected via the mouse.

I'll do some more experimenting...

Originally Posted by CDandVinyl

Ok thanks.

This does reinstate the automatic colour to C1:O1 , but not the gridlines. So the gridlines are blank.

I wonder if there is a way to copy the formatting from S1 also.

I ran it on sheet2 of my sample so you can see the result attached.

Try ColorIndex instead of Color

PHP Code: 

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Range("C1:O1").Interior
    If Intersect(Target, Range("A3:O27")) Is Nothing Then
        .ColorIndex = Range("S1").Interior.ColorIndex
    Else
        .Color = vbGreen
    End If
End With
End Sub 


Deleted...already stated above.

Thanks guys -that's working now. Grateful for your assistance.

There remains the issue of triggering the colour when the cell is selected via link rather than by mouse. By mouse , the colour is triggered. Selection by link doesn't trigger the colour.

Ok I've attached a sample file to illustrate the issue. Enter a search number in B4 to search in worksheets. Also click A13 to follow link.

The idea is to have C1:O1 show green on the affected worksheet when the search is successful and the cell is selected in the range A3:O27 , and clear it where no cell is selected in the range or the worksheet is closed.

Presently , where the search number is found in the worksheets , it highlights the cell , but does not trigger the colour in C1:O1. I'm trying to find a consistent application of the colour. It seems to work better on the first worksheet and less so on others.

Grateful for any advice.

Thnaks for getting back. I tried remming the enable / disable events switches but it's not resolving the issue I'm afraid.

Yes , that was my understanding.

I have already deleted lines from Worksheet_BeforeDoubleClick

Application.EnableEvents = False

and

Application.EnableEvents = False

but the colour is still not triggered in worksheets where the cells are selected via a link. It does seem to work on the first sheet , but not in others.

Maybe I'm not understanding. Perhaps a reworked version of the sample file with the fix in place would clarify.

Thanks for getting back. It still stubbornly resists here , I'm afraid.

As an example , it if enter '3456' into B4 on the first sheet and press return , it goes to Spare1!D12 and selects D12 in red. No green response in C1:O1.

Other presses and clicks do give a response , so it's strange that it only works sporadically.

At any rate , I'm grateful for your help guys , even though we couldn't quite get to the bottom of it.

Originally Posted by CDandVinyl

Thanks for getting back. It still stubbornly resists here , I'm afraid.

As an example , it if enter '3456' into B4 on the first sheet and press return , it goes to Spare1!D12 and selects D12 in red. No green response in C1:O1.

Other presses and clicks do give a response , so it's strange that it only works sporadically.

At any rate , I'm grateful for your help guys , even though we couldn't quite get to the bottom of it.

I think I understand your issue.

Don't forget that the selection change event on a sheet is only triggered if the selection CHANGES not on sheet activation.

So if you disable macros and select box 7780 on sheets Spare2 (for example) then save the workbook, box 7780 will still be the selected cell on that sheet and it will not show green because you disabled macros.
The next time you open your workbook, if you find box 7780, the selection will not be 'changed', so your selection change event will not trigger and you won't get those cells turned green.

This scenario will also cause your workbook open event to fail with an error 1004. To cover all bases, in addition to the code mods above, change your workbook open event to;

Please Login or Register  to view this content.

Thanks guys. I put the amendments in place and it's running fine now.

With search terms that are found it successfully makes C1:O1 green and cancels this on return to B4. This happens on all sheets which is perfect.

Small anomaly. When an unknown search term is entered , the message box fires and C1:O1 goes green. On cancelling the message box , the cursor returns to B4 but C1:O1 stays green and doesn't clear. Strange.

It would be better if C1:O1 were red for search terms that were not found at any rate , but I'm puzzled as to why it doesn't clear when it selects B4 after closing the message box.

.

Thanks jindon.

The issue occurs after a search is not found.

Enter 9999 to B4 and cancel the message box. C1:O1 goes green and doesn't cancel on return to B4.

I don't know why it doesn't cancel.

It should have no colour or go red after the message box.

Thanks GameChanger. Good idea!

I imagine amending intersects might affect some other behaviours though. Perhaps an intersect to deal with message box issue. I'll give it a go.

Originally Posted by CDandVinyl

Thanks GameChanger. Good idea!

I imagine amending intersects might affect some other behaviours though. Perhaps an intersect to deal with message box issue. I'll give it a go.

Well, it may well do. That is what testing is all about and you know the behaviour you want, we are just guessing.

You can disable events just after the message display (before the select B4), then enable events immediately after.'

Plenty of ways to skin this cat.

Yes , I agree.

After a successful search triggers the colours , it's the return to B4 which cancels the colours. This works perfectly.

After an unsuccessful search and the message box to show this , the return the B4 doesn't cancel the colours. So that's the area to test , as you say.

Presently , the intersect changes I'm trying are having wider effects than just on the unsuccessful search issue , but I'll keep at it. There will be a solution I'm sure , just a question of finding it.

OK I've managed to fix the issue where the colour was not resetting for unsuccessful searches.

I used the count of successful results in B7. If this resolves to zero then this code cancels the colour :

Please Login or Register  to view this content.

This resolves the issue without impacting any other function.

Thanks for your help guys , and for sticking with me. Problem solved!