Highlight Column When Cells In Range Are Active

[bhsoundman]

Hi All,

I'm trying to use Conditional Formatting to temporarily highlight multiple columns when any cell in those columns are active.

Getting a single column to do it is working fine with this formula:

=COLUMN()=CELL("col")

The basic set up:

Columns A:J are the columns that need to all highlight simultaneously when any cell in those columns are active.

Possible? Or do I need to use VBA?

Thanks in advance!

 

[mumps]

I'm not sure about conditional formatting but try this macro. Copy and paste the macro into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Click on any cell in A:J.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Intersect(Target, Range("A:J")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    ActiveSheet.Cells.Interior.ColorIndex = xlNone
    Range("A" & Target.Row).Resize(, 10).Interior.ColorIndex = 6
    Application.ScreenUpdating = True
End Sub

 

[bhsoundman]

I'm not sure about conditional formatting but try this macro. Copy and paste the macro into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Click on any cell in A:J.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Intersect(Target, Range("A:J")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    ActiveSheet.Cells.Interior.ColorIndex = xlNone
    Range("A" & Target.Row).Resize(, 10).Interior.ColorIndex = 6
    Application.ScreenUpdating = True
End Sub

Thanks so much for your reply. This code is working as you intended, however I'm looking to highlight columns A:J as opposed to the rows. I've tried making this line do it:
Range("A" & Target.Row).Resize(, 10).Interior.ColorIndex = 6
But I'm screwing up the syntax.

 

[mumps]

Try:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Intersect(Target, Range("A:J")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    ActiveSheet.Cells.Interior.ColorIndex = xlNone
    Columns(Target.Column).Interior.ColorIndex = 6
    Application.ScreenUpdating = True
End Sub

 

[bhsoundman]

Thanks so much for your reply. This code is working as you intended, however I'm looking to highlight columns A:J as opposed to the rows. I've tried making this line do it:
Range("A" & Target.Row).Resize(, 10).Interior.ColorIndex = 6
But I'm screwing up the syntax.

Thanks for your help.

 

[mumps]

You are very welcome.