Change colour of cell while active

julie_nickb

New Member
Joined
Sep 13, 2018
Messages
46
Office Version
  1. 365
In VBA, I would like to change the colour of the active cell, and reset it to its original colour when exited.
Can you help? Thanks!
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
This Worksheet_SelectionChange event procedure should do what you want (note that I guessed the color to change to is red)...
VBA Code:
Dim LastCellColor As Long, LastCellAddress As String

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If Len(LastCellAddress) Then Range(LastCellAddress).Interior.Color = LastCellColor
  LastCellAddress = ActiveCell.Address
  LastCellColor = ActiveCell.Interior.Color
  ActiveCell.Interior.Color = vbRed
End Sub
Note: That Dim statement at the top is not inside any procedures... I would suggest you place it at the top of the code window.

HOW TO INSTALL Event Code
------------------------------------
If you are new to event code procedures, they are easy to install. To install it, right-click the name tab at the bottom of the worksheet that is to have the functionality to be provided by the event code and select "View Code" from the popup menu that appears. This will open up the code window for that worksheet. Copy/Paste the event code into that code window. That's it... the code will now operate automatically when its particular event procedure is raised by an action you take on the worksheet itself. Note... if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.
 
Upvote 0
Solution
This Worksheet_SelectionChange event procedure should do what you want (note that I guessed the color to change to is red)...
VBA Code:
Dim LastCellColor As Long, LastCellAddress As String

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If Len(LastCellAddress) Then Range(LastCellAddress).Interior.Color = LastCellColor
  LastCellAddress = ActiveCell.Address
  LastCellColor = ActiveCell.Interior.Color
  ActiveCell.Interior.Color = vbRed
End Sub
Note: That Dim statement at the top is not inside any procedures... I would suggest you place it at the top of the code window.

HOW TO INSTALL Event Code
------------------------------------
If you are new to event code procedures, they are easy to install. To install it, right-click the name tab at the bottom of the worksheet that is to have the functionality to be provided by the event code and select "View Code" from the popup menu that appears. This will open up the code window for that worksheet. Copy/Paste the event code into that code window. That's it... the code will now operate automatically when its particular event procedure is raised by an action you take on the worksheet itself. Note... if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.
I must be doing something wrong - the colours of the cells I click change to red, but don't go back to the original colour when I leave.
 
Upvote 0
The code was tested before I posted it and it works correctly. Did you follow the directions I posted so that the code is located in the correct code module?
 
Upvote 0
The code was tested before I posted it and it works correctly. Did you follow the directions I posted so that the code is located in the correct code module?
I made a mistake and placed the "Dim LastCellColor As Long, LastCellAddress As String " inside the Sub.
Now it almost works - only one problem: When I activate the sheet, I activate a cell as well. This first activated cell remains red.
 
Upvote 0
I made a mistake and placed the "Dim LastCellColor As Long, LastCellAddress As String " inside the Sub.
Now it almost works - only one problem: When I activate the sheet, I activate a cell as well. This first activated cell remains red.
 
Upvote 0
I think I fixed it. I put this code before activating the cell
LastCellAddress = ActiveCell.Address
LastCellColor = ActiveCell.Interior.Color

THanks for your help.
 
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,289
Members
452,631
Latest member
a_potato

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top