View Code - VBcolor

DPChristman

Board Regular
Joined
Sep 4, 2012
Messages
126
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

MrExcel MVP
Joined
May 26, 2009
Messages
16,666
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

Board Regular
Joined
Sep 4, 2012
Messages
126
Essentially, just the active cell. This way, the user can identify what cell they will be typing in.

For example:
Store #: Location:
00000LOCATION - CITY, STATE


<colgroup><col span="5"></colgroup><tbody>
</tbody>
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

MrExcel MVP
Joined
May 26, 2009
Messages
16,666
Essentially, just the active cell. This way, the user can identify what cell they will be typing in.

For example:
Store #: Location:
00000LOCATION - CITY, STATE

<tbody>
</tbody>
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

Board Regular
Joined
Sep 4, 2012
Messages
126
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

MrExcel MVP
Joined
May 26, 2009
Messages
16,666
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

Board Regular
Joined
Sep 4, 2012
Messages
126
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

MrExcel MVP
Joined
May 26, 2009
Messages
16,666
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,504
Office Version
2010
Platform
Windows
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".
Code:
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
 
Last edited:

Forum statistics

Threads
1,081,702
Messages
5,360,743
Members
400,595
Latest member
T_Dubs

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top