# Excel 2007 change color of selected cells



## Nexus37

I have a worksheet open in Excel 2007 and I want to edit several cells at the same time. I hold down my CTRL kb button and click on multiple cells. As I click on the cells, the background color of these cells temporarily changes color so that you can easily see which cells you have selected. I would like to change that color but cannot find where I do that. I think I have checked all the tabs and option screens, colors and themes but I'm sure that I have missed it. Can someone please help me do this? Thanks.


----------



## oldmn

Is it the same as excel 2003 you select the cells then right click then select format cells , goto the patterns tab and select the color you want?


----------



## Glaswegian

Hi and welcome to TSF.

I know to what you are referring, but I don't believe you can change the highlighted background colour. AFAIK that is a built in Excel feature and cannot be changed, although I'm not familiar with 2007, so that could be something they changed for that version. If so then I missed hearing about it.


----------



## Nexus37

Oldmn - thanks for the reply. I think you may be thinking about shading/fill which I can do. The change I need is only temporary and only shows when a cell is selected.

Glaswegian - thanks to you as well. I'm sorry to read that it may not be changable. I think you know exactly what change I'm referring to. Currently, the existing color is very light and darkening it would be helpful. I know that there are a lot of smarter people than I out there so I'm remaining hopeful that someone may know the answer or if we get a "2nd" to Glaswegian's response!


----------



## Zazula

First of all Glas is right - the shading which indicates cell selection is built-in and AFAIK cannot be tweaked. However may I ask: are these specific cells white or do they have a color background?


----------



## Nexus37

Zazula - thanks for replying.
No, these are your basic cells w/o shading or color. I'm pretty knowledgeable about a lot of MSO functions and tweaks (surely not as much as you guys) but I was completely stumped with this one.
I think you have confirmed what Glaswegian said earlier. Well, I had to try!
Thanks to everyone for helping the handicapped!


----------



## Zazula

The reason why I asked is because if the visibility of the selected cells is not practical for you or you simply don't like it, you might also want to consider trimming your screen's Contrast and Brightness settings a bit, to see if you can reach a more visible effect.


----------



## Nexus37

For me, I think the color/visibility of the selected cells is too light. Adjusting the contrast might just work. I will try that.
Thanks again for your help


----------



## coffey21

Hi All,

sorry to pick up on this thread again, but think it's the best description of this problem.

Just would like to add to Nexus' problem; I use the find option in excel a lot, and also use it frequently in a range of selected cells (ie, you have the value you're looking for in multiple places but just want to quickly find it in one column)

In that case I select the column (ctrl+space), hit (ctrl+F), enter my value and hit Enter. In those cases the selected cells are very lightblue (as Nexus described), but the found cell is just a little bit lighter. It's almost impossible to see it and see where you are on the screen.

Hope a solution will come, but honestly find this a big flaw in enduserfriendlyness (if that's a word).

edit - Try it, and you know exactly what I mean - end edit

Anyway, thanks for hearing my complaint.

rgds,
Coffey


----------



## cougar548

Nexus37 - 

I understand your delima and it has been driving me nuts! Since we can't physically make the change in the program, I found a way to trick it into the change; use VBA! Here's the code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Cells.FormatConditions.Delete
With Target
.FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
.FormatConditions(1).Interior.ColorIndex = 24 '35
End With
End Sub

You can really choose any color you want (this is a great site for color reference: http://http://www.mvps.org/dmcritchie/excel/colors.htm) 

Hope this works for you. Worked great for me!

Chad


----------



## coffey21

Cougar548 - 

Thanks a lot, tried it and works great for me too. Also that link you sent is really worth it's money.

Decided, after years of laziness to put some more effort in my VBA skills. You made it look so simple.

Thanks again,
Coffey


----------



## CherokeeBlue

cougar548 said:


> Nexus37 -
> 
> I understand your delima and it has been driving me nuts! Since we can't physically make the change in the program, I found a way to trick it into the change; use VBA! Here's the code:
> 
> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> Cells.FormatConditions.Delete
> With Target
> .FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
> .FormatConditions(1).Interior.ColorIndex = 24 '35
> End With
> End Sub
> 
> You can really choose any color you want (this is a great site for color reference: http://http://www.mvps.org/dmcritchie/excel/colors.htm)
> 
> Hope this works for you. Worked great for me!
> 
> Chad



Can anyone explain how to implement the above code so that it runs every time I use Excel 2007? I have practically no experience in VBA.

TIA!


----------



## CherokeeBlue

Nevermind, I figured it out for the current worksheet.

However, how do I implement it as a default setting so that it works for any workbook?


----------



## kmham

can someone explain in detail how to insert cougar's VB code? I know very, very little about VB so please provide detailed instructions of where this gets copied. I've tried it in the VBAProject->Microsoft Excel Objects->"Sheet1" and "ThisWorkbook" and haven't had any luck getting it to work. And as CherokeeBlue mentions, where can (if anywhere) this code be inserted so it works each and every time that I open and use Excel 2007? Thanks to everyone that contributed to this post.


----------



## tracer123

i used the VB code above and it worked great for the 1 specific worksheet, howver how do i use this for the entire workbook, also is there a way to keep the grid lines visable when using this code without having to create borders on the cells

thank you in advance for any help


----------



## suzzan

go to the office tab and scroll down to Excel Options, and when the panel opens, you can customize it.

You have a choice of a blue, black, or silver color scheme.

suzzan


----------



## BampaOwl

I too have struggled with both of these glitches for a year or so (selection behaviour and find behaviour), and am pleased to find co-sufferers and a possible solution.

But as a TOTAL stranger to VBA (I used to use original Basic :sigh, I really would appreciate a hint or two on using Cougar548's fix so it's always there when I want it - which is always!

(What were Micro$oft thinking of?)

Cheers, Alan



cougar548 said:


> Nexus37 -
> 
> I understand your delima and it has been driving me nuts! Since we can't physically make the change in the program, I found a way to trick it into the change; use VBA! Here's the code:
> 
> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> Cells.FormatConditions.Delete
> With Target
> .FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
> .FormatConditions(1).Interior.ColorIndex = 24 '35
> End With
> End Sub
> 
> You can really choose any color you want (this is a great site for color reference: http://http://www.mvps.org/dmcritchie/excel/colors.htm)
> 
> Hope this works for you. Worked great for me!
> 
> Chad


----------



## Wendark

Hi,

I'm not a technical person by any stretch of the imagination, but I am persistant and I think I have a solution for emphasizing the color of selected/find items.

In Display Properties>Appearance>Advanced, under "Item", I selected "Window" and changed the color to a light grey (it was white). Now when I select cells, they show as a darker grey. I hope to get used to the silvery background, but at least I can see the multiple cell selections I've made.

Hope this helps others.


----------



## BampaOwl

EUREKA! Many thanks Wendark, for a practical fix that works and is easily applied by numpties like me - which is more than I can say for some of the stuff posted by the techies. I too am not sure about the silver-grey background on EVERYTHING, but one could always swap around depending on whether Excel is in use.

Incidentally, I find the selected/found cells do show upa bit better on my laptop than on my desktop - obviously depends on the screen characteristics.

Alan


----------



## Daveinton

1st post - hope I'm in the right place!

Installing Cougar's VB (suggest you do this with a blank worksheet first to make sure I've got it right!)

1. In Excel, click Alt-F11 to open the VB window
2. Click Ctrl-R to open the Project Explorer tab.
3. In the Project Explorer tab, choose the sheet you want to install the code in. If more than one, you can repeat steps 3 & 4 as many times as required - there's probably a neater way to do it, but I'm just going through the steps that worked for me 
4. Click F7 to open the 'View Code' pane and paste the VB code in.
5. Go back to the workbook and select some cells and they should now be a million times more noticeable.
6. When you insert it into your live workbook, you'll need to save the changes before closing Excel.

OK, now onto my own little problem. I've installed Cougar's VB code and it works as it should - unfortunately, my s/s has a lot of conditional formatting which is all removed as soon as I select any cells - is it possible to modify the code to get around this?

Thanks
Dave


----------



## RSpecianjr

Hey Dave,

Welcome to TSF!

I was just working on a very similar problem with highlighting the active cell.

Highlight active row in Excel

You may be looking for something like this:



Code:


Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range)
Static OldRng As Range

On Error Resume Next
If Application.CutCopyMode = xlCopy Or Application.CutCopyMode = xlCut Then
OldRng.FormatConditions(1).Delete
Exit Sub
End If

OldRng.FormatConditions(1).Delete

'Conditional Formatting for Selected Cell
Target.FormatConditions.Add Type:=xlTextString, String:= _
"A Very Improbable String Of Text To Be Found In Your Workbook.", TextOperator _
:=xlDoesNotContain
Target.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Target.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
End With
Target.FormatConditions(1).StopIfTrue = False

Set OldRng = Target
Application.CutCopyMode = False
End Sub

This will put the conditional formatting in, then move that conditional format to the number 1 spot. Knowing that it is in that spot, we can later reference and remove just that formatting.

Hope this helps,

Robert Specian Jr.


----------



## Daveinton

Hi Robert,

Thanks for your help. 
The code works fine in making the highlighting of selected cells more visible, but there now seems to be a further problem. What appears to happen is that if any of the selected cells already contain conditional formatting, when I then select a cell away from that area, the original selection remains highlighted. Furthermore, if at any point I make a new selection which includes one or more of these highlighted cells, the new selection also remains highlighted when I click away from it! So as time goes by, more and more of the worksheet becomes permanently highlighted! Cells with no conditional formatting do not exhibit this behaviour i.e. the highlighting is removed when I click away from them.

Help!
Dave.


----------



## macropod

Whilst I'm no expert in this area, here are some links you folk might find useful:
http://www.mrexcel.com/hof001.php
http://www.mrexcel.com/forum/showthread.php?t=443483


----------



## RSpecianjr

Hey macropod,

Thanks for posting, I was at wits end. 

Just to add. The best I can see is that there is an issue with programmatically removing conditional formats when there are two or more conditional formats with varying ranges. There doesn't seem to be a issue when they share the same range.

Thanks again macropod!

Robert Specian Jr.


----------



## Daveinton

Morning comrades,

Thanks for your assistance. I've had a look at the 'mrexcel' links but unfortunately the solution there addresses a different problem.

Just to clarify, my problem is identical to that experienced by Nexus37 and coffey21 except that it affects me when I use the Ctrl-G (Go To) function to show up cells which contain formulae and which Cougar548's solution addresses perfectly - as long as there's no existing conditional formatting present. My worksheet (approx 70 cols by 3500 rows) has a large amount of conditional formatting which has to stay in place.

So, in the absence of an alternative solution, I'll muddle along with my 'User2' screen profile which makes things a touch better at least.

Once again, thanks for all your help.

P.S. At least I've found an answer to that age-old question - whether to upgrade (???) at home to MSO2007!


----------



## RSpecianjr

Hey Daveinton,

Correct, the problem should effect you no matter how you select the cells. Whether it be using the goto function, a mouse click, or even through a macro. The only alternative I can think of at the moment is making maybe editing the current conditional formatting ranges. Meaning this:

You select a cell, if there is conditional formatting in it, the code will get the range of that CF and then chop it into 2 CF's. One for the range you selected, the other for whatever other range it was covering. Then, it could add the 'Selection' CF. After you select another cell, it could piece the other conditional format back together.

I don't have the time right now to test to see if that will work. However, that would be the next way I approach it.

I'm stumped if that doesn't work. =/

Regards,

Robert Specian Jr.


----------



## Corday

Thread reopened at request of LevinioRM.


----------



## pcride

You might want to try adjusting your monitor contrast and brightness. Excel 2007 and 2010 have a different theme than 2013
And 2016. Personally the color themes and ability to customize lacks big time. Also the default contrast in Outlook is very poor. I don't have a huge problem in the earlier versions but I know how that is bothersome! 2016 is getting to be more like 2010 in the way the menus are and how the cell selector flows from one cell to the next using the arrow keys.


----------

