Solved

hi-lighting a range of cells based on current cell selection

Posted on 2013-06-22
7
264 Views
Last Modified: 2013-06-24
I am working with a large table and would like a range of cells to be hi-lighted when I select a specific cell.
For example...my table is B2:M15000
If I select cell M5 it would hi light cells B5:K5 orange (or whatever color)
If I then select cell M37, the formatting in range B5:K5 would return to its original formatting and it would then hi-light cells B37:K37 orange (or whatever color)...and so on.

Thanks,

Edwin
0
Comment
Question by:gixxer1020
7 Comments
 
LVL 22

Expert Comment

by:Flyster
ID: 39268732
Right-Click the tab with your data and select View Code. Enter this code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Address = Range("M5").Address Then
        Range("B5:K5").Select
            With Selection.Interior
             .ThemeColor = xlThemeColorAccent6
         End With
    End If
End Sub

Open in new window

This will give you the first result you asked for (Selecting M5 will Highlight B5:K5) Add the other ranges as needed.
Flyster
0
 

Author Comment

by:gixxer1020
ID: 39268822
Thanks Flyster,

But i will need to apply this to 15000 rows

Thanks,

Edwin
0
 
LVL 14

Expert Comment

by:Faustulus
ID: 39268823
Please install this code on the code sheet of the worksheet in which you wish to have the action (Right-click on the tab and select 'View code")
Option Explicit

Private Enum Nws                ' Worksheet navigation
    NwsFirstDataRow = 2         ' don't highlite above this row
    NwsStart = 2                ' start highlite in column (B = 2)
    NwsEnd = 11
    NwsTest = 13                ' column M = 13
End Enum
    
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Const FillColor As Long = vbYellow
    ' also available: vbRed, vbGreen, vbBlue, vbYellow, vbMagenta, vbCyan
    
    Dim Rng As Range
    
    With ActiveSheet.Cells.Interior
        .Pattern = xlNone
    End With
    
    With Target
        If .Column = NwsTest Then
            If .Cells.Count = 1 And .Row >= NwsFirstDataRow Then
                With .Worksheet.Rows(.Row)
                    Set Rng = Range(.Cells(NwsStart), .Cells(NwsEnd))
                End With
                With Rng.Interior
                    .Pattern = xlSolid
                    .PatternColorIndex = xlAutomatic
                    .Color = FillColor
                End With
            End If
        End If
    End With
End Sub

Open in new window

You may make some adjustments to this code:-
The 4 enumerations at the top can be modified as required. They now define that you don't want any highlighting in row 1 (above NwsFirstDataRow) and point to the columns of B:K for highlighting and M for testing.
A little further down you have the constant 'FillColor' now set to vbYellow (which has a value of 65535). You can select one of the other enumerated colors Excel offers or insert a number of your own research.
None of the above changes require modification of the code that does the work.

Note that this code will remove all coloring from the worksheet except Conditional Formatting every time you change the selection. If you do have other coloring that mustn't be removed the programming will become considerably more complex because the program would need to keep track of which row you last highlighted.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:gixxer1020
ID: 39268855
Thanks Faustulus,

I do have columns L and M hi-lighted in green...how would I just hi-light those two columns at the end of the code?

Thanks,

Edwin
0
 
LVL 14

Accepted Solution

by:
Faustulus earned 350 total points
ID: 39268908
Brilliant idea, Edwin.
The revised code isn't only more to the point it also manages to be shorter:-
Option Explicit

Private Enum Nws                ' Worksheet navigation
    NwsFirstDataRow = 2         ' don't highlite above this row
    NwsStart = 2                ' start highlite in column (B = 2)
    NwsEnd = 11
    NwsTest = 13                ' column M = 13
End Enum
    
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Const FillColor As Long = vbYellow
    ' also available: vbRed, vbGreen, vbBlue, vbYellow, vbMagenta, vbCyan
    
    Dim Rng As Range
    
    With ActiveSheet
        Set Rng = Range(.Columns(NwsStart), .Columns(NwsEnd))
    End With
    Rng.Interior.Pattern = xlNone

    With Target
        If .Column = NwsTest Then
            If .Cells.Count = 1 And .Row >= NwsFirstDataRow Then
                With Rng.Rows(.Row).Interior
                    .Pattern = xlSolid
                    .PatternColorIndex = xlAutomatic
                    .Color = FillColor
                End With
            End If
        End If
    End With
End Sub

Open in new window

Instead of wipe the color from the entire sheet it just removes the fill in columns B:K, leaving your colored columns L:M untouched.
0
 
LVL 81

Assisted Solution

by:byundt
byundt earned 150 total points
ID: 39269951
A somewhat different approach is to add the cells in columns B to K to the selection if one or more cells in column M are selected. With this approach, you don't need to worry about overwriting any existing formatting.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim cel As Range, targ As Range
Set targ = Range("B2:K1500")  'These cells will be highlighted if a cell in same row in column M is selected
Set cel = Intersect(Columns("M"), Target)
If (Not cel Is Nothing) Then
    Set targ = Intersect(targ, cel.EntireRow)
    If Not targ Is Nothing Then
        Application.EnableEvents = False
        Union(targ, cel).Select
        cel.Activate
        Application.EnableEvents = True
    End If
End If
End Sub

Open in new window

0
 

Author Closing Comment

by:gixxer1020
ID: 39272019
Thanks Faustulus, that works great.

Byundt, your code works great as well however with Excel 2007's inherent issues of a "barely noticeable cell selection hi-light" I will choose to go with the code provided by Faustulus as I can choose the color of the cell selection hi-light.

Thank you both,

Edwin
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

932 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

14 Experts available now in Live!

Get 1:1 Help Now