Solved

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

Posted on 2013-06-22
7
300 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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 

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

Industry Leaders: 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!

Question has a verified solution.

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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

623 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