need a cell(s) highlighted when clicking on another cell in the same row/column

carchbold91

New Member
Joined
Jul 25, 2014
Messages
4
Hello,

I am working a spreadsheet for my job in which I need certain cells in the same row/column to be highlighted when clicking on another cell. For example, if I click on D9, I want cells B9 (same row) and D1 (same column) to be highlighted a given color. I want this same rule to be implied for range C4;T17. I don't the whole row/column highlighted, just the given cells. I tried looking for codes online but I have been unsuccessful thus far. Please someone help me resolve this issue or give me a code I could use.

P.S : For the example I gave earlier, it would be helpful as well if someone could tell me how to highlight cell D2 as well (along wit cell B9 and D1), if clicked on cell D9.

Thanks,
Chris
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

John Davis

Well-known Member
Joined
Sep 11, 2007
Messages
3,457
Maybe:

Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'ActiveSheet.UsedRange.Interior.ColorIndex = xlNone - Optional
If Target.address = "$D$9" Then
    Cells(Target.row, 2).Interior.ColorIndex = 6
    Cells(1, Target.Column).Interior.ColorIndex = 6
    Cells(2, Target.Column).Interior.ColorIndex = 6
End If
End Sub
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,409
See if this does what you need

Put the code below in the Worksheet Module

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Static previousCell As Range
       
    If Not previousCell Is Nothing Then
        If Not Intersect(Range("C4:T17"), previousCell) Is Nothing Then
            Cells(previousCell.Row, "B").Interior.Color = xlNone
            Cells(1, previousCell.Column).Interior.Color = xlNone
        End If
    End If
            
    If Not Intersect(Range("C4:T17"), Target) Is Nothing Then
        Cells(Target.Row, "B").Interior.Color = vbYellow
        Cells(1, Target.Column).Interior.Color = vbYellow
    End If
    
    Set previousCell = Target
End Sub

Hope this helps

M.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,893
Messages
5,598,712
Members
414,254
Latest member
MarieCo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top