How can I select a cell in Excel and it highlights other chosen cells?

Question

I'm creating a schedule of sorts. I want to be able to click on a cell and have it highlight several other specific cells chosen at my whim.

Example: I want to select B3 and have it highlight C3 and K4. But I also want to click on either C3 or K4 and have it highlight the other two. In the same sheet I'd like to select C12 and have it highlight L5 and N19; C12 L5 and N19 will highlight the others whichever one is chosen.

Answer 1

To achieve the functionality of selecting a cell in Excel and highlighting other specific cells, you can use VBA (Visual Basic for Applications) to create a macro. This macro can be designed to respond to cell selections and highlight the desired cells accordingly. Here’s a basic example of how you can set this up:

1. Open the VBA Editor: Press ALT + F11 in Excel to open the VBA editor.
2. Insert a Module: Right-click on any of the items in the Project Explorer, go to Insert, and then click Module.
3. Add the Following Code:

      Private Sub Worksheet_SelectionChange(ByVal Target As Range)
          ' Clear previous highlights
          Cells.Interior.ColorIndex = xlNone
      
          ' Highlight cells based on selection
          If Not Intersect(Target, Range("B3")) Is Nothing Then
              Range("C3, K4").Interior.Color = RGB(255, 255, 0) ' Yellow
          ElseIf Not Intersect(Target, Range("C3, K4")) Is Nothing Then
              Range("B3, C3, K4").Interior.Color = RGB(255, 255, 0) ' Yellow
          ElseIf Not Intersect(Target, Range("C12")) Is Nothing Then
              Range("L5, N19").Interior.Color = RGB(255, 255, 0) ' Yellow
          ElseIf Not Intersect(Target, Range("L5, N19")) Is Nothing Then
              Range("C12, L5, N19").Interior.Color = RGB(255, 255, 0) ' Yellow
          End If
      End Sub
   

4. Close the VBA Editor: Save your work and close the editor.
5. Test the Macro: Now, when you click on the specified cells (B3, C3, K4, C12, L5, N19), the corresponding cells will highlight in yellow.

This code uses the Worksheet_SelectionChange event to check which cell is selected and highlights the specified cells accordingly. You can modify the ranges and colors as needed for your schedule.

---

References:

• Highlight the Active Cell, Row, or Column
• Selecting and Activating Cells

Answer 2

Hi Sam, I get what you’re trying to do. Excel doesn’t natively highlight “linked” cells when you click one, because selection isn’t something conditional formatting can detect on its own. The fix is to use a tiny macro that records the address of the currently selected cell in a named cell, then set conditional formatting rules that light up the matching group. Here’s a quick way to set it up.

Pick an unused cell on the sheet, like Z1. Name it SelectedAddr by clicking in the Name Box and typing SelectedAddr. Create a conditional formatting rule for your first group and apply it to the whole sheet: use a formula like =OR(CELL("address",B3)=$SelectedAddr, CELL("address",C3)=$SelectedAddr, CELL("address",K4)=$SelectedAddr) and choose a fill color. Do the same for the second group: =OR(CELL("address",C12)=$SelectedAddr, CELL("address",L5)=$SelectedAddr, CELL("address",N19)=$SelectedAddr). Now add this code to the worksheet’s code module so the named cell updates whenever you click:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    On Error Resume Next

    ThisWorkbook.Names("SelectedAddr").RefersToRange.Value = Target.Address(True, True)

End Sub

Enable macros, then click B3, C3, or K4 and the other two will highlight. Click C12, L5, or N19 and that trio will light up. If you prefer not to use a helper cell, I can show a version that handles this entirely in VBA, but it can overwrite existing fills. Are you working in Excel for Windows or Mac, and are macros allowed in your environment? Also, will you need more groups, and should each group use a different color?