Highlight selected cell

[Corleone]

I would like to insert some code into my spreadsheet that does the above but only when
the selected cell is in column C - As i dont want it to overwrite the conditioning formats in the other columns

so effecitvly the macro will only run when a cell in column c is selected

any help appreciated

thanks

 

[tigeravatar]

Corleone,

Give the following a try:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    Me.Columns("C").Interior.ColorIndex = xlNone
    On Error Resume Next: Intersect(Target, Me.Columns("C")).Interior.ColorIndex = 6
    
End Sub

Hope that helps,
~tigeravatar

 

[Corleone]

Thanks Brilliant!!

Thanks

 

[Corleone]

Just spotted an issue with this - when it is included as code within the worktab

it seems to disable the copy and paste facility

is their any way around this?

could i modify the code so that it run only when a macro is selected?

thanks

 

[tigeravatar]

Corleone,

I'm not sure what you mean by "it seems to disable the copy and paste facility" I just ran several copy/paste tests, on worksheet and in code, and all were successful. Can you give more information?

 

[Corleone]

Corleone,

I'm not sure what you mean by "it seems to disable the copy and paste facility" I just ran several copy/paste tests, on worksheet and in code, and all were successful. Can you give more information?

i copied the code into a blank worksheet tab "sheet 01" and it works fine (it highlights the selected cell in column c as yellow), however, if i then try to (for example copy some text from cell b2 into cell b3, nothing happens) i am able to select the cell and copy, but the paste option
is greyed out.

using the same file - if i go into "sheet02" (where i havent put the code in) the copy and paste function works fine



i am using excel 2003

cheers

 

[tigeravatar]

Unfortunately, I'm unable to duplicate your issue, although I am using 2007 and don't have access to my 2003 atm to double-check there. Sorry

 

[Corleone]

no problem thanks for your help

if anyone have 2003 and can have a go please let me know

thanks

much appreciated

 

[GTO]

In 2000:

If you do not mind losing PasteSpecial (this will paste values only anyways), you might try this (contrived from Corleone's here and Aflatoon's solution at http://vbaexpress.com/forum/showthread.php?t=39107 )

<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br>    <br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Declare</SPAN> <SPAN style="color:#00007F">Function</SPAN> CloseClipboard <SPAN style="color:#00007F">Lib</SPAN> "User32" () <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Declare</SPAN> <SPAN style="color:#00007F">Function</SPAN> OpenClipboard <SPAN style="color:#00007F">Lib</SPAN> "User32" (<SPAN style="color:#00007F">ByVal</SPAN> hWnd <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>     <br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_SelectionChange(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br><SPAN style="color:#00007F">Dim</SPAN> blnClip <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN><br>    <br>    <SPAN style="color:#00007F">If</SPAN> Application.CutCopyMode <> <SPAN style="color:#00007F">False</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        blnClip = <SPAN style="color:#00007F">True</SPAN><br>        OpenClipboard 0&<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <br>    Me.Columns("C").Interior.ColorIndex = xlNone<br>    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> Next: Intersect(Target, Me.Columns("C")).Interior.ColorIndex = 6<br>    <br>    <SPAN style="color:#00007F">If</SPAN> blnClip <SPAN style="color:#00007F">Then</SPAN> CloseClipboard<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>