CODEWITHSUNDEEP
excelvba
A Phillips
A Phillips

Reputation: 23

Changing the value of a cell when all cells in a range equal the same value

I’m trying to create a macro to change the value of a single cell only when all cells in a range are of a specific value. I’ve looked around and it seems I can only do this with a worksheet change macro based on a variant. Based on this, I have put together the following:

Sub Worksheet_Change() 

Dim VarItemName As Variant
VarItemName = Range("Other_Checks!G85:G87")
Dim Value As String

Application.EnableEvents = False
If Range("Other_Checks!G85, Other_Checks!G86, Other_Checks!G87").Value = "N/A" Then
    Range("Other_Checks!G88").Value = "N/A"
Else
    Range("Other_Checks!G88").Value = "Pending"
    Application.EnableEvents = True
End If
End Sub

The problem is it only seems to work if the first cell in the range (G85) changes (regardless of the value of the other 2 cells). What am I doing wrong?

Upvotes: 2

Views: 1335

Answers (1)

Alex P
Alex P

Reputation: 12487

This works for me using the Worksheet_Change event.

You'll need to substitute your range references.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim VarItemName As Range, cl As Range

    If Not Intersect(Target, Range("A1:C1")) Is Nothing Then
        If Range("A1") = "n/a" And Range("B1") = "n/a" And Range("C1") = "n/a" Then
            Range("A2") = "n/a"
        Else
            Range("A2") = "Pending"
        End If
    End If
End Sub

By the way, this could easily be done using an IF formula on the spreadsheet. It may be an option to consider?

Upvotes: 1

Related Questions