CODEWITHSUNDEEP
excelvba
mod_phnx
mod_phnx

Reputation: 1

Change a cell value in a sheet based on the value of another sheet cell value

I have two sheets in a workbook, "Sheet1" and "Sheet2".

I want sheet2 cell A1 value to be either string "Potato" or "Tomoato" based on the cell value of A1 or A2 in sheet1 with a condition.

e.g.

if A1 (sheet1) >= 7 or A2 (sheet1) >= 7 then
    A1(sheet2) = "Potato"
Else 
    A1(sheet2) = "Tomato"

I already having code in sheet1.

Dim xVal, yVal As String

Private Sub Worksheet_change(ByVal Target As Range)
    Static xCount As Integer
    Static yCount As Integer
    Application.EnableEvents = False
    If Target.Address = Range("C28").Address Then
        Worksheets("sheet2").Range("T3").Offset(xCount, 0).Value = xVal
        xCount = xCount + 1
    Else
        If xVal <> Range("C28").Value Then
         Worksheets("sheet2").Range("T3").Offset(xCount, 0).Value = xVal
        xCount = xCount + 1
        End If
    End If

    If Target.Address = Range("C24").Address Then
        Worksheets("sheet1").Range("U3").Offset(yCount, 0).Value = yVal
        yCount = yCount + 1
    Else
        If yVal <> Worksheets("Main").Range("C24").Value Then
            Worksheets("sheet1").Range("U3").Offset(yCount, 0).Value = yVal
            yCount = yCount + 1
        End If
    End If
    Application.EnableEvents = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    xVal = Range("C28").Value
    yVal = Range("C24").Value
End Sub

Upvotes: 0

Views: 182

Answers (1)

Nicola Morganti
Nicola Morganti

Reputation: 11

Sorry, but if your target is:

if A1 (sheet1) >= 7 then A1(sheet2) = "Potato" Else A1(sheet2) = "Tomato"

you don't need vba at all. You just simply put in A1(sheet2) cell directly the IF function in this way:

=IF(Sheet1!A1>=7;"Potato";"Tomato")

and that's it :)

Upvotes: 1

Related Questions