Active Cell White... - Excel Table

The attached spreadsheet tab "QuestionCode" has a table with two colors that I want to keep those colors, except when the cell is the active cell.  The code in the sheet changes the active cell to white, but does not change it back to the original color when the active cell is changed.  What changes to the code would enable this to happen?  Thanks.  - Tom
Copy-of-EE.xlsm
LVL 7
tomfarrarAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:
Use this code...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Application.EnableEvents = False

    Application.ScreenUpdating = False
    Range("A1:E19").Interior.ColorIndex = 37
    Range("F1:K19").Interior.ColorIndex = 6
    Target.Interior.ColorIndex = xlNone
    Application.ScreenUpdating = True
    Application.EnableEvents = True

End Sub

Open in new window


Saurabh...
Ejgil HedegaardCommented:
Or using the colour the cell had before.
Option Explicit
Dim PrevColor As Long, rg As Range

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Application.ScreenUpdating = False
    If Not rg Is Nothing Then
        If Not rg.Address = Target.Address Then
            rg.Interior.Color = PrevColor
        End If
    End If
    PrevColor = Target.Interior.Color
    Set rg = Target
    Target.Interior.ColorIndex = xlNone
    Application.ScreenUpdating = True
End Sub

Open in new window

Roy CoxGroup Finance ManagerCommented:
Take a look at this example.

Excel Ruler
Excel-Ruler.xls
Exploring ASP.NET Core: Fundamentals

Learn to build web apps and services, IoT apps, and mobile backends by covering the fundamentals of ASP.NET Core and  exploring the core foundations for app libraries.

tomfarrarAuthor Commented:
Thanks to all of you for your responses.  Just a few questions:

Saurabh -  The macro works.  If I wanted the original color to change to other than:

Range("A1:E19").Interior.ColorIndex = 37
Range("F1:K19").Interior.ColorIndex = 6

How do I determine what number to use?  Is there a table somewhere?  - Thanks

Egjil - Your macro works except when I am done moving the curser around, and come back to the page later, the final cell is still white and remains that way after I move the curser.  What can I do to make sure the final move reverts back to the original color?  Thanks.

Roy_Cox - I can't seem to get your macro to work.  Is there something I might be missing?  Thanks.
Roy CoxGroup Finance ManagerCommented:
It works in my example so have you put the code into the correct place? I have converted the code into an addin as well that allows it to be used in any open workbook. I can let you have a copy if you want.
tomfarrarAuthor Commented:
Sure, that would be great.  I had just copied the code into the worksheet, and it did not work for me for some reason.  Please let me see what you've got.  - Tom
Roy CoxGroup Finance ManagerCommented:
I'll attach it tomorrow. with instructions on how to install the addin
Saurabh Singh TeotiaCommented:
tomfarrar,

You can simply use a function which is like...

function color(rng as range)
color=rng.interior.colorindex
end function

Open in new window


Now Insert the above function in the new module of your workbook.. by inserting one.. Now come back to your excel workbook.. do =color(a1) this will give you the color index of cell a1 and that way you can change accordingly in the code...

Saurabh...
Ejgil HedegaardCommented:
Revised version.
After last move, click on a cell somewhere outside the coloured area to restore the last cell.

If you save and close the workbook with a cell in the coloured area selected, the workbook opens next time with that cell without colour.
Since there is no previous colour for the cell when the workbook has been closed, it can't be restored.
The ranges could be predefined for specific colours to be set at workbook open, but then the flexibility for range sizes and individual cell colour used is lost.

The colour of the cells surrounding the cell could be found, and if it looks like the sample with ranges with the same colour, the cell could be coloured with that. Not easy but possible.
But if the coloured ranges are inside another coloured range the result would be wrong.
See file with yellow cells inside the blue range.

Instead select 2 other cells in the same colour region with the mouse.
Use the format brush on the 2 cells, and then select the not coloured cell to give it the same format.
Or select 2 cells and set the colour manually.
The program only change to no colour when just 1 cell is selected.

But best use the method by selecting a not coloured cell as last move.
EE-1-change-cell-color-back-2.xlsm
Roy CoxGroup Finance ManagerCommented:
Here's the addin that I promised. It's got a few other tools in itas it's just something I work on when I have the time. The addin has it's own Tab in the Ribbon called SHEET MANAGER, Just switch the ruler on when you want to use it with the checkbox in that Tab.

How to install an addin
Sheet-Manager--EE-v1-.xlam
tomfarrarAuthor Commented:
Thanks, Roy, but I was still not able to get the add-in to identify the cell as white as I moved around the colored group of cells.  Maybe next time.  - Tom

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Roy CoxGroup Finance ManagerCommented:
It highlights the selected cell's row and column light blue.
tomfarrarAuthor Commented:
Thanks, Roy.  I wanted just the selected cell to be highlighted in white where the other cells were another color.  I hope we cross paths again.  You are working on some interesting things.  - Tom
tomfarrarAuthor Commented:
Thanks to all of you for showing me many options.  - Tom
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.