CODEWITHSUNDEEP
excelvbaformatting
Thomas Fonn
Thomas Fonn

Reputation: 113

How to highlight multiple cells based on value on click in Excel

Context

The Excel file is made up of 2 parts.

  1. A regular data set (Picture included below)
  2. 2D-representation of multiple machine modules and their corresponding cable trenches.

The Task

I'd like to highlight the corresponding cells in the 2D-representations when I click on an ID in the data set.

This mockup-image shows the desired effect. Image of multiple selected fields with duplicates that should be filled As seen in the image, there are multiple cells with duplicate values in the 2D-represenatation that needs highlighting

How would one go about doing something like this?

Upvotes: 0

Views: 332

Answers (2)

Thomas Fonn
Thomas Fonn

Reputation: 113

As Alex suggested, the Worksheet_SelectionChange Event was a the way to go. It is now working as intended.

Working as intended

Here's what I wrote. 

\\Constructor
\\ResetColors
\\MakeTrenchYellow subroutines here.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Column = 1 And 3 - Selection.Cells.Count > 1 Then
        Constructor
        ResetColors

        Dim SelectedRowTextjoin As String
        SelectedRowTextjoin = Target.Offset(0, 6).Value

        Dim CurrentResult As Variant
        CurrentResult = Split(SelectedRowTextjoin, ", ")

        Dim AmountOfElements As Integer
        For Each Item In CurrentResult
            AmountOfElements = AmountOfElements + 1
        Next

        For i = 1 To AmountOfElements
            MakeTrenchYellow (CurrentResult(i - 1))
        Next i
    End If
End Sub

Upvotes: 0

Alex de Jong
Alex de Jong

Reputation: 1267

You should use VBA as Jerry says. I would look at the Worksheet_SelectionChange Event To trigger the highlighting and use a for-loop to go trough the columns to check if the corresponding cell needs to be highlighted.

Upvotes: 2

Related Questions