View Code - VBcolor

[DPChristman]

I have a spreadsheet (form,really) that contains a lot of automatically updated fields from other spreadsheets.

Because of all the activity in this document, I don't want to lock any cells.

However, to give my users the ability to identify the current active cell.

I found an used this Worksheet_SelectionChange event by right clicking the tab, selecting view code, and pasting this

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.ScreenUpdating = False
Cells.Interior.ColorIndex = 0
Target.Interior.Color = vbCyan
Application.ScreenUpdating = True
End Sub

It works as advertised, but for some reason, it leaves all the other previously shaded rows un-shaded.

Is there a way to just highlight only the active cell?

The above script is supposed to do it, but apparently not

 

[JoeMo]

The code you posted removes ALL color from ALL cells, then highlights the active cell by filling it with cyan. What sort of "highlight" do you want?

 

[DPChristman]

Essentially, just the active cell. This way, the user can identify what cell they will be typing in.

For example:
[TABLE="width: 620"]
<colgroup><col span="5"></colgroup><tbody>[TR]
[TD]Store #: [/TD]
[TD="colspan: 4"]Location: [/TD]
[/TR]
[TR]
[TD]00000[/TD]
[TD="colspan: 4"]LOCATION - CITY, STATE

[/TD]
[/TR]
</tbody>[/TABLE]
They would type in only the store number, and everything else would be auto-filled.

i don't want to have to protect the entire document, and un-protect certain cells, because the document requires the user to be able to edit headings were needed.

So the best option I though of would be to at least allow the document to highlight the cell they are currently in.

 

[JoeMo]

Essentially, just the active cell. This way, the user can identify what cell they will be typing in.

For example:
[TABLE="width: 620"]
<tbody>[TR]
[TD]Store #: [/TD]
[TD="colspan: 4"]Location: [/TD]
[/TR]
[TR]
[TD]00000[/TD]
[TD="colspan: 4"]LOCATION - CITY, STATE
[/TD]
[/TR]
</tbody>[/TABLE]
They would type in only the store number, and everything else would be auto-filled.

i don't want to have to protect the entire document, and un-protect certain cells, because the document requires the user to be able to edit headings were needed.

So the best option I though of would be to at least allow the document to highlight the cell they are currently in.

That's what the code you posted does.

 

[DPChristman]

Well, no. Because according to your initial response, 'The code you posted removes ALL color from ALL cells'. Because my document is a form for the users to fill out, the headers are shaded depending on the section of the form, etc. I don't want the color removed from any cells. I just want when the click into a cell they are going to type in, it highlights.

 

[JoeMo]

Well, no. Because according to your initial response, 'The code you posted removes ALL color from ALL cells'. Because my document is a form for the users to fill out, the headers are shaded depending on the section of the form, etc. I don't want the color removed from any cells. I just want when the click into a cell they are going to type in, it highlights.

In that case, just remove this line:

Cells.Interior.ColorIndex = 0

 

[DPChristman]

I actually thought of, and tried that.

When you remove Cells.Interior.ColorIndex = 0, it still highlights the active cells, but they stay highlighted after you go on to the next cells, not just the active cell

 

[JoeMo]

I actually thought of, and tried that.

When you remove Cells.Interior.ColorIndex = 0, it still highlights the active cells, but they stay highlighted after you go on to the next cells, not just the active cell

Will the user always make a change to the active cell? If yes, you can highlight the cell when it is chosen and remove the highlight after the change is made.

Alternatively, if the users are restricted to a range outside the range that has your color-filled cells, you can change the existing code to remove all color just from that range before highlighting the active cell.

 

[Rick Rothstein]

Maybe this event code will work the way you want... it will only color selected cells (that are not already colored) with the vbCyan (actually, one more than that color value so that it does not interfere with normally cyan colored cells) and leaves all colored cells "untouched".

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Application.FindFormat.Clear
  Application.ReplaceFormat.Clear
  Application.FindFormat.Interior.Color = vbCyan + 1 'one more than vbCyan's value
  Application.ReplaceFormat.Interior.Color = xlNone
  Cells.Replace "", "", SearchFormat:=True, ReplaceFormat:=True
  Application.FindFormat.Clear
  Application.ReplaceFormat.Clear
  Application.FindFormat.Interior.Color = xlNone
  Application.ReplaceFormat.Interior.Color = vbCyan + 1 'one more than vbCyan's value
  Target.Replace "", "", SearchFormat:=True, ReplaceFormat:=True
  Application.FindFormat.Clear
  Application.ReplaceFormat.Clear
End Sub

 

[DPChristman]

thanks joe.

i think Ricks answer works the best for me.