Solved

MS Excel Cell Selection Color - highlighting using VBA

Posted on 2010-09-01
24
1,211 Views
Last Modified: 2012-05-10
How does Excel achieve the selection highlighting? How would I do this in VBA? In other words how would  I change the interior color to look like it was selected? But not actually selecting. And not disturbing existing interior colors or conditional formating, so that I can undo myHighliting leaving the appearance as it was? Does it add/subtract some number from the interior color?
Regards
Brian
0
Comment
Question by:canesbr
  • 10
  • 7
  • 5
  • +2
24 Comments
 
LVL 13

Expert Comment

by:MWGainesJR
Comment Utility
Not sure I follow you.....
You can change a cells interior color in VBA like this
Range("A1").interior.color = vbred
0
 
LVL 24

Expert Comment

by:broomee9
Comment Utility
If your conditional formatting rule is met, this formatting will supercede any formatting you add manually or through code.  So even if you set the background to vbRed, if your conditional format is met and your background color is blue, it will stay blue until the condition is no longer met.
0
 

Author Comment

by:canesbr
Comment Utility
Say you have a couple blue cells, a couple red cells, some cells with fancy conditional formatting bargraphs, and whatever. Then you select a region. So what you see is a faint blue highlight over the entire area selected, and the preexisting blue and red and graphed cells go slightly darker. And if you change your selection or hit ESC then you goes back to seeing what you saw befaw.
So my question is how to do that kind of highlighting myself in VBA.
0
 
LVL 13

Expert Comment

by:MWGainesJR
Comment Utility
You would have to have VBA select the cells
Range("A1:C20").select
this will actually select it though.....no other way that i'm aware of without actually selecting it manually or programmaticaly
0
 
LVL 18

Expert Comment

by:WarCrimes
Comment Utility
You could create a drawing object that had a translucent background and position this object over the cells you want to cover to "fake" the selection.

The code for this is not straightforward, and I don't have the time right at the moment to draw it up, but I'm throwing the idea out there for anyone who does.

WC
0
 

Author Comment

by:canesbr
Comment Utility
@WarCrimes (wow) Placing an object over the cells would prevent a user from easily getting under it in order to type or whatever.
@MWGainesJR Actual selection is not what is wanted. For example, it disappears if the window containing the selection is not the focus.

If Excel can do it, why not we mere mortals?
0
 
LVL 13

Expert Comment

by:MWGainesJR
Comment Utility
we can do it.....its called selecting lol
You're wanting the look and feel of selecting without selecting....it's just not programitcally possible.
0
 
LVL 13

Expert Comment

by:MWGainesJR
Comment Utility
If this doesn't work, I know of no other way to do it.  Just change rng to whatever you want:
Sub Simulate_Selecting()



    Dim rng As Range

    Set rng = Range("B2:D10")

    With rng.Interior

        .Pattern = xlSolid

        .PatternColorIndex = xlAutomatic

        .ThemeColor = xlThemeColorAccent1

        .TintAndShade = 0.799981688894314

        .PatternTintAndShade = 0

    End With

    rng.Borders(xlDiagonalDown).LineStyle = xlNone

    rng.Borders(xlDiagonalUp).LineStyle = xlNone

    With rng.Borders(xlEdgeLeft)

        .LineStyle = xlContinuous

        .ThemeColor = 4

        .TintAndShade = 0.799981688894314

        .Weight = xlThin

    End With

    With rng.Borders(xlEdgeTop)

        .LineStyle = xlContinuous

        .ThemeColor = 4

        .TintAndShade = 0.799981688894314

        .Weight = xlThin

    End With

    With rng.Borders(xlEdgeBottom)

        .LineStyle = xlContinuous

        .ThemeColor = 4

        .TintAndShade = 0.799981688894314

        .Weight = xlThin

    End With

    With rng.Borders(xlEdgeRight)

        .LineStyle = xlContinuous

        .ThemeColor = 4

        .TintAndShade = 0.799981688894314

        .Weight = xlThin

    End With

    With rng.Borders(xlInsideVertical)

        .LineStyle = xlContinuous

        .ThemeColor = 4

        .TintAndShade = 0.799981688894314

        .Weight = xlThin

    End With

    With rng.Borders(xlInsideHorizontal)

        .LineStyle = xlContinuous

        .ThemeColor = 4

        .TintAndShade = 0.799981688894314

        .Weight = xlThin

    End With

    With rng.Borders(xlEdgeLeft)

        .LineStyle = xlContinuous

        .ColorIndex = 0

        .TintAndShade = 0

        .Weight = xlMedium

    End With

    With rng.Borders(xlEdgeTop)

        .LineStyle = xlContinuous

        .ColorIndex = 0

        .TintAndShade = 0

        .Weight = xlMedium

    End With

    With rng.Borders(xlEdgeBottom)

        .LineStyle = xlContinuous

        .ColorIndex = 0

        .TintAndShade = 0

        .Weight = xlMedium

    End With

    With rng.Borders(xlEdgeRight)

        .LineStyle = xlContinuous

        .ColorIndex = 0

        .TintAndShade = 0

        .Weight = xlMedium

    End With

End Sub

Open in new window

0
 

Author Comment

by:canesbr
Comment Utility
@MWG Thanks, but that overrides cells that allready have interior colors. I want to have a way that I can do and then later undo the colors. Maybe subtracting ?20 from R and ?x from G and ?-z from B? The RGBs from a few colors are shown below. No specific pattern seems obvious. So would it be a case of maintaining a table of RGB values, then looping through the desired region and testing the interior color and then switching to the selected or unselected RGB?
                      Selected                        Unselected            
                     R      G      B            R      G      B
[Color 46)            234      137      80            255      102      0
[Color 47)            134      137      179            102      102      153
[Color 48)            165      168      177            150      150      150
[Color 49)            68      104      146            0      51      102
[Color 3]           234   71    80           255  0      0    
This seems like a brute force method and would be a bear to set up. So what I am looking for is the algorithm that Excel uses to adjust the colors.
0
 
LVL 13

Expert Comment

by:MWGainesJR
Comment Utility
This is beyond my coding ability.....you're gonna need one of the insanely genius programmers on here to even understand how that's done.
0
 
LVL 85

Expert Comment

by:Rory Archibald
Comment Utility
I don't believe Excel does adjust the colours of the cells - I imagine it actually draws over that part of the window instead, using API calls. 
0
 

Author Comment

by:canesbr
Comment Utility
Well, suppose discovering how Excel does selection highlighting is not going to happen any time soon - because for some deep dark reason nobody with a high enough clearance is available to reveal this, what is the best way to simulate it?
This should now be in a separate question?
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 85

Expert Comment

by:Rory Archibald
Comment Utility
Why do you want to?
0
 

Author Comment

by:canesbr
Comment Utility
Why do I want to do a new question? Or why do I want to simulate highlighting?
I'll take question 2 for 500, Alex. Answer: Daily Double!
When you select a region, for example, Excel highlights the row and column headings. and provides selection highlighting with a transparent color and heavy bordering. If you ctrl-select then there is no fat bordering. Now, when the window is not in focus, the highlighting is no longer visible. Or, if you turn off row/column headings you no longer have that marker, and if you say use the vertical or horizontal scroll to move away so that the selection is not in the current visible range, then you can't see what is selected at all. So I want to highlight a cross-hair of row/column from cell 1 to  xlLastCell using the selectionChange event. This solves tha above problems, plus it allows the visual clue of all values in the r/c in which the user has made a selection - quite very useful. So the problem is not destroy existing interior colors or fancy conditional formatting, but to be able to turn on or off highlighting in the zact same way that Excel does selection highlighting. I have a brute force method, but I am looking for the kinder gentler approach.
You did ask, did you not?
Regards
Brian
0
 
LVL 85

Expert Comment

by:Rory Archibald
Comment Utility
Well, I don't know what your brute force approach is, but there isn't anything that I would call a gentle approach. (though I wouldn't be in your position to start with...)
Have you tried Chip Pearson's Row/column highlighter add-in? Or is that what you are doing now?
0
 

Author Comment

by:canesbr
Comment Utility
The Pearson thing draws a shape over the columns which shapes tend to get in the way of things. (Getting under to change a cell, having to nudge things around, bla, ble, bli,..)
My b-f method is to have a 'shadow' sheet into which I paste the formatting of the subject sheet. This allows restoring what was there before I change the internal colors (what is this colours thang? - you a hoser?) So this means I would have to maintain two sheets for every one in which I want the highlighting thang. Instead, I was looking for the Excel method. But, again, failing that, a gentler approach would be to find a formula that would adjust/unadjust the RGB by calculated amounts. Thus, no tables of RGB and no shadow sheets. (How low can you go - lower than the shadow of shark sheet on the ocean floor...?)
Uh, what is my position to start with...? And why would you not be in it?
Gentle regards
Brian
0
 
LVL 85

Expert Comment

by:Rory Archibald
Comment Utility
No idea what a hoser is - I just speak the Queen's English.
"Your position" is requiring something like this; I never have and I would never have row or column headers hidden on a sheet where I need to know what rows/columns I have selected.
Adjusting cell colours won't work if you have conditional formatting that applies colour - it will override whatever you do, unless you actually remove the CF.
Seems to me like this is a lot of work and complication for very little gain, but perhaps that's just me.
0
 

Author Comment

by:canesbr
Comment Utility
A hoser is a kind of Canadian maybe.

Many situations. Lots of gain. See again my comments.

Are there points for persuading users not to do what they allegedly stupidly want to do, or learn what they want to learn only if they can justify it? This happens when I do that. Well don't do that. Problem solved.

Even gentler regards
Brian
0
 
LVL 85

Expert Comment

by:Rory Archibald
Comment Utility
No, no points, but then they aren't important anyway.
I'm merely trying to ascertain why you would be in a position to need this (so far you've only really told that you are, not why), and to let you know there isn't a simple solution. To get what you seem to want would require API calls (GDI related) and be very complicated, IMO. There may well be others who have the time and inclination to figure it out, though. :)
0
 

Author Comment

by:canesbr
Comment Utility
Ok I did say why, but it matters not, this is what I want to achieve.
So, no, I don't right away agree that it is very complicated and needs a bunch of alphabet soup. Forgetting about how MS/Excel does it for now.
It is a RGB color question now - how to take a color and adjust its RGB values so it looks like it has the transparent highlight.
If you select a bunch of cells that already have a bunch of fill colors you will see new colors. This is plainly visible. And, gosh darn it, each of those has their RGB. And the RGB(new)= RGB(rOld_-rdelta, gOld+-gDelta, bOld+-bDelta).

BTW, IMHO you didn't get the 'point' in my last comment.
Regards
Brian
0
 
LVL 85

Expert Comment

by:Rory Archibald
Comment Utility
Did you note my comment about conditional formatting?

Since we appear to be two people separated by a common language, I think I'll wish you luck and leave it there.

Regards,
Rory
0
 

Accepted Solution

by:
canesbr earned 0 total points
Comment Utility
Yes, some conditional formats will take precedence. That's ok.

I am sorry you feel that way. Why would you want to do that?

Kind regards
Brian
0
 
LVL 85

Expert Comment

by:Rory Archibald
Comment Utility
Because to get a good solution would take more time and effort than I can spare, and I don't want to produce a 'solution' that will still take a lot of work and only work some of the time.
Rory
0
 

Author Comment

by:canesbr
Comment Utility
I have a working solution. (Please see attached)
So no work is expected.
Just some ideas or discussion as to given a light color how to make it darker, and given a dark color how to make it lighter by adjusting the RGB

Respectfully
Brian
'Currently sheet Project has this code.

'Sheet Shadow is initialized by copying Project and PastSpecialing Formats into Shadow

'Reinitialize if the structure and colors of the base Project sheet is revised



Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If [highlight.stop] Then Exit Sub

    If (Target.Rows.Count > 1) Or (Target.Columns.Count > 1) Then Exit Sub

    If ActiveWindow.SelectedSheets.Count > 1 Then

        MsgBox "Multiple sheets selected - row and column highlighting is not advised"

        Exit Sub

    End If

    doApplication False

        doRefresh Target.SpecialCells(xlLastCell).Row, Target.SpecialCells(xlLastCell).Column

        doHighlight Target

        Target.Select

    doApplication True

End Sub

Sub doHighlight(hCell As Range)

    doRecolor Range(Cells(1, hCell.Column), Cells(hCell.SpecialCells(xlLastCell).Row, hCell.Column))

    doRecolor Range(Cells(hCell.Row, 1), Cells(hCell.Row, hCell.SpecialCells(xlLastCell).Column))

End Sub

Sub doRecolor(xHair As Range)

    With xHair.Interior

        .Pattern = xlSolid

        .PatternColorIndex = xlAutomatic

        .ThemeColor = xlThemeColorAccent6

        .TintAndShade = 0.799981688894314

    End With

End Sub

Sub doRefresh(EndRow, EndColumn)

    Sheets("Shadow").[A1].Resize(EndRow, EndColumn).Copy

    Sheets("Project").[A1].Resize(EndRow, EndColumn).PasteSpecial Paste:=xlPasteFormats

End Sub

Sub doApplication(what As Boolean)

    Application.ScreenUpdating = what

    Application.EnableEvents = what

End Sub

Open in new window

0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now