Solved

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

Posted on 2013-06-22
7
251 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
Comment Utility
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
Comment Utility
Thanks Flyster,

But i will need to apply this to 15000 rows

Thanks,

Edwin
0
 
LVL 14

Expert Comment

by:Faustulus
Comment Utility
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
Free Trending Threat Insights Every Day

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.

 

Author Comment

by:gixxer1020
Comment Utility
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
Comment Utility
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 80

Assisted Solution

by:byundt
byundt earned 150 total points
Comment Utility
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
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
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…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

762 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

6 Experts available now in Live!

Get 1:1 Help Now