Solved: Active Cell Help

[FJGLASGOW]

Hi there, I have very limited knowledge of macros so hopefully someone can help. I have created an excel workbook with 5 sheets, when I use the sheet I want the active cell to be coloured / highlighted. From a google search I came up with this -


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
For Each cell In Range("A1:M80")
If cell.Address <> ActiveCell.Address Then
cell.Interior.ColorIndex = xlNone
Else
cell.Interior.ColorIndex = 3
End If
Next
End Sub


It works a treat when the workbook is not protected but as soon as I protect it an error appears. Any clues? its driving me mad. :banghead:

FJ

[FJGLASGOW]

Anybody ???

[mdmackillop]

Welcome to VBAX
This will allow code changes to the sheets, but not user changes

Private Sub Workbook_Open()
For Each Sh In Worksheets
Sh.Protect userinterfaceonly:=True, Password:="pw"
'Sh.Unprotect Password:="pw"
Next
End Sub

[FJGLASGOW]

Thank you, still having problems thought.

When I enter my code from the first post the active cell is coloured blue. If I then click - TOOLS>PROTECTION>PROTECT SHEET , click a cell to make it active I get the following error.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
For Each cell In Range("A1:M80")
If cell.Address <> ActiveCell.Address Then
cell.Interior.ColorIndex = xlNone
Else
cell.Interior.ColorIndex = 34
End If
Next
End Sub
Am I being really stupid ? I tried entering mdmackillop code below my own but it makes no difference.

Cheers

FJ

[GTO]

Greetings,

Malcom's example was meant to be put in the ThisWorkbook Module. If you placed it "below" your code, it is in the worksheet module and will not be run.

In VBIDE (the code window) look over to (normally) the upper left window (the Project Explorer window) and look for ThisWorkbook right below however many sheets you have. Double-click ThisWorkbook and insert Malcom's code there.

BTW, change "pw" to whatever actual password you are using for your worksheets.

Does that help?

Mark

[FJGLASGOW]

Thanks guys almost there, would it be possible to send a copy of my spreadsheet to someone to help with the last little bit (much easier than explaining over an email)

Thank you

FJ (p.s does the site keep crashing?)

[Bob Phillips]

You can post it here. Click Go Advanced, and then the Manage Attachments button.

[FJGLASGOW]

Cheers, Still doesnt seem to work I am affraid any one one give it a go ?

passwork 'backsneddon'

[Bob Phillips]

When you say 'almost there' what exactly do you mean? I have just opened that workbook and it does nothing. Malcolm's code is not there, nor is your sheet code.

I added Malcolm's code, and then your code into Build-Up (2) and it (seemed to) work fine.

[FJGLASGOW]

Sorry I included the master sheet with no code, when I put in both codes I still get an error ? do you mind adding the code exactly where it going as re attach it to the thread?

Thank you

[mdmackillop]

Try this

[FJGLASGOW]

Thank you very much for your help an assistance !!!

What a great forum

[FJGLASGOW]

Hi again guys, my spreadsheet is still working great but I have one slight problem. I cant seem to copy and past the content of any unlocked cells. The copy command works ok but the Paste function is blanked out. Any suggestions ? Do I need to add something to my Macro?

[GTO]

...The copy command works ok but the Paste function is blanked out...

Greetings,

Unfortunately, I think that this is to do with the highlighting of the active cell that you wanted. See, if you comment this out,

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
' For Each cell In Range("A1:M80")
' If cell.Address <> ActiveCell.Address Then
' cell.Interior.ColorIndex = xlNone
' Else
' cell.Interior.ColorIndex = 3
' End If
' Next
End Sub

you'll see that the Paste option is still available. With the above code in effect, if you watch carefully, you'll see that after copying, when you select another cell, those funny little blinking marks on the copied cell disappear. This indicates that the copy has been dumped and is the norm for Excel when you make changes after copying.

Does that make sense?

Mark

[FJGLASGOW]

Cheers for that, I understand what you are saying but does anyone know anyway round it?

Thank you for your help

[FJGLASGOW]

Anyone able to help please? Or would I be best removing the code to highlight the box ??

Cheers FJ

[Aussiebear]

Please post your workbook?

[GTO]

Well, not perfect, but this seems to work...

Definitely in a tossaway copy case you don't like; but this basically cancels if in Cut mode (so no one can delete all the borders) and skips the highlighting (so the copymode is not cancelled) while in copy mode.


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Application.CutCopyMode = 2 Then Application.CutCopyMode = False

If Not Application.CutCopyMode = 1 Then

For Each cell In Range("A1:M80")
If cell.Address <> ActiveCell.Address Then
cell.Interior.ColorIndex = xlNone
Else
cell.Interior.ColorIndex = 3
End If
Next
End If
End Sub


After done copying/pasting, press the Esc button, and the next cell selection change should go back to highlighting the active cell.

Hope that helps,

Mark

[FJGLASGOW]

Thank you , that seems to work great....