Link to home
Start Free TrialLog in
Avatar of Tom Farrar
Tom FarrarFlag for United States of America

asked on

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
SOLUTION
Avatar of Saurabh Singh Teotia
Saurabh Singh Teotia
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Take a look at this example.

Excel Ruler
Excel-Ruler.xls
Avatar of Tom Farrar

ASKER

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.
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.
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
I'll attach it tomorrow. with instructions on how to install the addin
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...
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
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
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
It highlights the selected cell's row and column light blue.
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
Thanks to all of you for showing me many options.  - Tom