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

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
gixxer1020Asked:
Who is Participating?
 
FaustulusConnect With a Mentor Commented:
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
 
FlysterCommented:
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
 
gixxer1020Author Commented:
Thanks Flyster,

But i will need to apply this to 15000 rows

Thanks,

Edwin
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
FaustulusCommented:
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
 
gixxer1020Author Commented:
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
 
byundtConnect With a Mentor Commented:
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
 
gixxer1020Author Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.