Solved

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

Posted on 2013-06-22
7
294 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

734 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