CODEWITHSUNDEEP
excelvbaconditional-statementshighlight
SeánMcK
SeánMcK

Reputation: 422

Highlight cell conditional on another cell being clicked

I have VBA code that works, but does not seem to be optimal. The code should change the colour of the text in the relevant cell in columns H & I when a cell in Column N is clicked.

For example, when cell N5 is clicked, the text in cells H5 and I5 should turn white. When it is unclicked, they return to their normal colour.

The code does not seem to be optimal because the change in column I lags behind that in column H.

I would like a way to make both change instantaneously.

(Bonus points if you can make the cells change colour AND turn into bold instaneously, with the constraint that like for colour, the bold disappears once the cell is unselected).

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Dim r As Range

  Intersect(Columns("H"), ActiveSheet.UsedRange).Font.Color = vbBlack
  Set r = Intersect(Range("N:N"), Target)
  If r Is Nothing Then Exit Sub
  Cells(r.Row, "H").Font.Color = vbWhite

  Intersect(Columns("I"), ActiveSheet.UsedRange).Font.Color = vbBlack
  Set r = Intersect(Range("N:N"), Target)
  If r Is Nothing Then Exit Sub
  Cells(r.Row, "I").Font.Color = vbWhite

End Sub

Please note, this is my first time writing VBA, hence any amateurismes.

Upvotes: 0

Views: 176

Answers (1)

Tim Williams
Tim Williams

Reputation: 166306

You don't need to address each column separately...

EDIT: added bold and multiple cells

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim r As Range, c As Range

    Set r = Intersect(Me.Range("N:N"), Target)
    '? what to do if user selects multiple cells ?
    'E.g. - exit if user has >100 cells selected (possibly whole column)
    If r Is Nothing Or Target.Cells.CountLarge > 100 Then Exit Sub

    Application.ScreenUpdating = False

    HighlightIt Application.Intersect(Me.Range("H:I"), Me.UsedRange), False

    For Each c In r.Cells
        HighlightIt Me.Cells(c.Row, "H").Resize(1, 2)
    Next c

    Application.ScreenUpdating = False
End Sub

'utility sub for highlighting/unhighlighting
Sub HighlightIt(rng As Range, Optional hilite As Boolean = True)
    With rng
        .Font.Color = IIf(hilite, vbWhite, vbBlack)
        .Font.Bold = hilite
    End With
End Sub

Always worth thinking about what should happen if the user selects multiple cells (or even a whole column). Handling this robustly is sometimes a challenge, depending on what you want to happen when they do that.

Upvotes: 1

Related Questions