VBA to add text to cell, based on the value of a cell in another sheet.

[JEH105]

Hello everyone! I don't even know if this is possible, but worth a shot? Thank you for your time in advance!

I need help with a VBA code that can add "COVERING" text to an empty cell in Sheet 2, based on the value of Sheet 3. It will be for specific columns, for example Sheet 1, Column G based on the data on Sheet 3, column G. The text should only be added to the cell to which the criteria applies. (e.g. Cell G15 in sheet 2 has "8", then Cell G15 in sheet 3 gets "COVERING" text added.)


This is what I have so far, but it's not working:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Sheet2.Range("$G10:$G270").Value > 1 Then
    Sheet3.Range("$G10:$G270").Value = "Covering"
    End If
    
End Sub

Important:

• Other text will be added/included in both sheet columns (e.g. "Holiday", "U", etc.)
• I only want the cells with number values over 0 to be counted for this VBA in sheet 2. (Ignore the text values)
• If the cell in Sheet 3 has a text/value in place beforehand, I'd like the VBA to be ignored. However, if the cell is cleared, the VBA should add the text according to the criteria.
• If the value in Sheet 2 is deleted, then the same should happen on Sheet 3.
• There may be more than 2 columns compared in the same spreadsheet. Example:
  • Sheet 2, Column G with Sheet 3, Column G
  • Sheet 4, Column K with Sheet 3, Column P
  • etc.
• Another auto calculate VBA is included in the same sheet with the same name. Hence, I get ambiguous name error.

 

[JEH105]

Okay, so maybe my post question is confusing, but I'm still stuck ?... so here it goes:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("J10").Value > 1 Then
    Range("F10").Value = "Testing"
    Else
End If
End Sub

I'm trying to use this VBA I found but I need:

• To check the value Column J, Sheet10, cell J9 and under. Only check for numeric values.
• If numeric value is greater than 1, add "Testing" text to Column F, Sheet 10, cell F9 and under.
• If numeric value is removed from check column, to remove the "Testing" text.

This works great on one sheet, but I can't find anything to switch it to another sheet. :/

Thank you again everyone, any help is appreciated!

 

[BalloutMoe]

An example to help clarify your second post. Still kind of lost. What sheet and column and what not

 

[BalloutMoe]

Something like this maybe? Just to get you started out

Private Sub testing()

Dim Cell As Range
 
 For Each Cell In Range("J1:J10")

If Application.WorksheetFunction.IsNumber(Cell) = True And Cell.Value > 1 Then
Cell.Offset(0, -4).Value = "Testing"
    'MsgBox "The value in cell is numeric"
Else
    Debug.Print "Value is less than one"

End If
Next
End Sub

 

[JEH105]

An example to help clarify your second post. Still kind of lost. What sheet and column and what not

Of course:

Value to be checked: Sheet 10, Column J, Cell 9-268.
Text to be added on: Sheet 11, Column F, Cell 9-268.
Criteria: Add "Testing" text if numeric value is greater than 1.

Example:

Sheet 10, Column J, Cell 10 = 8
Sheet 11, Column F, Cell 10 = "Testing"

If "8" value is deleted, so is the text "Testing" in the other sheet.

I wish I could post a mini-sheet, but my work laptop is limited to what I can post.

 

[JEH105]

Something like this maybe? Just to get you started out

Private Sub testing()

Dim Cell As Range
 
 For Each Cell In Range("J1:J10")

If Application.WorksheetFunction.IsNumber(Cell) = True And Cell.Value > 1 Then
Cell.Offset(0, -4).Value = "Testing"
    'MsgBox "The value in cell is numeric"
Else
    Debug.Print "Value is less than one"

End If
Next
End Sub

Thanks, I tried this one, but it didn't work. I think because the sheets are not referenced?

 

[BalloutMoe]

Thanks, I tried this one, but it didn't work. I think because the sheets are not referenced?

Yes you will need to reference the correct sheets

 

[BalloutMoe]

Here you go, please look at the notes and change names and numbers accordingly

Private Sub testing()

Dim wb As Workbook
Dim ws10 As Worksheet, ws11 As Worksheet

Set wb = ThisWorkbook
Set ws10 = wb.Worksheets("Sheet10") 'Change Sheet name accordingly
Set ws11 = wb.Worksheets("Sheet11") 'Change Sheet name accordingly

Dim Cell As Range, Cell2 As Range
 For i = 1 To 10 'Change numbers accordingly
Set Cell = ws10.Range("J" & i)
Set Cell2 = ws11.Range("F" & i)

If Application.WorksheetFunction.IsNumber(Cell) = True And Cell.Value > 1 Then
Cell2.Value = "Testing"
    'MsgBox "The value in cell is numeric"
Else
    Debug.Print "Value is less than one"

End If
Next
End Sub

 

[BalloutMoe]

I am not familiar with a way to get it to automatically update without running the code. What you can do is clear the range before you start. So add ws11.Range("F9:F268").ClearContents to go around it. It will clear the content and re run the test

 

[BalloutMoe]

Private Sub testing()

Dim wb As Workbook
Dim ws10 As Worksheet, ws11 As Worksheet

Set wb = ThisWorkbook
Set ws10 = wb.Worksheets("Sheet10") 'Change Sheet name accordingly
Set ws11 = wb.Worksheets("Sheet11") 'Change Sheet name accordingly

'Clear column F then proceed
ws11.Range("F1:F10").ClearContents

Dim Cell As Range, Cell2 As Range
 For i = 1 To 10
Set Cell = ws10.Range("J" & i)
Set Cell2 = ws11.Range("F" & i)

If Application.WorksheetFunction.IsNumber(Cell) = True And Cell.Value > 1 Then
Cell2.Value = "Testing"
    'MsgBox "The value in cell is numeric"
Else
    Debug.Print "Value is less than one"

End If
Next
End Sub