VBA Excel Highlighting cells based on cell input

Question

I'm trying to create a VBA script to highlight a particular range of cells when a user inputs any value in the cell. For example my cell range will be a1:a5, if a user enters any value in any cells within the range, cells a1 till a5 will be highlighted in the desired color. I'm a new user with VBA and after searching for a while found the below code that might be useful. Looking for advice. Thanks.

Private Sub Highlight_Condition(ByVal Target As Range)

Dim lastRow As Long
Dim cell As Range
Dim i As Long
With ActiveSheet
lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Application.EnableEvents = False
For i = lastRow To 1 Step -1

If .Range("C" & i).Value = "" Then
    Debug.Print "Checking Row: " & i
    .Range("A" & i).Interior.ColorIndex = 39
    .Range("F" & i & ":AW" & i).Interior.ColorIndex = 39
Next i
  Application.EnableEvents = True
End With
End Sub

Edit: Trying to edit the code given by teylyn to be able to remove highlight from cells if cell value is removed however I can't seem to find the solution. (The original code will highlight the cells when there is input in cells however if you remove the cell value the highlight remains there.)

If Not Intersect(Target, Range("A12:F12")) Is Nothing Then

    With Range("A12:F12").Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With

ElseIf IsEmpty(Range("A12:F12").Value) = True Then
       With Range("A12:F12").Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65536
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
End If

teylyn teylyn · Accepted Answer · 2018-07-17T06:40:36

This code does what you describe, i.e. set a fill color for range A1 to A5 when any cell in that range is edited.

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("A1:A5")) Is Nothing Then

    With Range("A1:A5").Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With

End If
End Sub

This code needs to be put in the sheet module.

Edit: If you want the highlight to disappear if none of the five cells have a value, then you can try out this variant:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim valCount As Long

If Not Intersect(Target, Range("A1:A5")) Is Nothing Then
' a cell in Range A1 to A5 has been edited
' we don't know if that edit was adding or deleting a cell, so ...
' ... we count how many cells in that range contain values

valCount = WorksheetFunction.CountA(Range("A1:A5"))

    If valCount > 0 Then
    ' the range has values, so highlight
        With Range("A1:A5").Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .Color = 65535
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
    Else
    ' the range has no values, so remove the highlight
        With Range("A1:A5").Interior
            .Pattern = xlNone
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
    End If
End If
End Sub

VBA Excel Highlighting cells based on cell input

1 Answers