突出显示以要单击的另一个单元格为条件的单元格 [英] Highlight cell conditional on another cell being clicked

问题描述

我有可以工作的VBA代码，但似乎不是最佳选择。该代码应更改H&列中相关单元格中文本的颜色。 I，当单击列N中的单元格时。

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.

例如，当单击单元格N5时，单元格H5和I5中的文本应变为白色。

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.

代码似乎不是最佳的，因为I列的更改落后于H列。

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

请注意，这是我第一次编写VBA，因此是业余爱好者。

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

推荐答案

您不需要分别解决每一列...

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

编辑：添加了粗体和多个单元格

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.

这篇关于突出显示以要单击的另一个单元格为条件的单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！