VBA Private Sub Column Criteria

[Pinaceous]

Good Day All,

I'm working with Column H upon my sheet, where if I enter a value into the cell's of this column; I'd like the corresponding cells of Column I to produce an interior color red with the words "Help!".

For example, image:

Please let me know, if you can help me with a code for this criteria.

Many thanks!
pinaceous

 

[Pinaceous]

Yes, but there seems to be other circumstances not accounted for.

1.
You earlier said ..

.. and I presumed after "then" came enter "Help!" and colour red.
I also assumed that if column H has a number and column I is not blank then do nothing. So, going the other way, suppose that column H has a number and column I contains something other than "Help!" and then column H is cleared, what should happen?

2.
What should happen if column H contained something other than a number and is then cleared. Is that possible and, if so, what should happen?

Ah, I see and apologize for not clarifying and see how this can be conflicting.

1. Column H has a number and column I is not blank then do nothing. But if in going the other way, in supposing that column H has a number and column I contains something other than "Help!" & interior red color then also do nothing.

2. Column H will not contain anything other than a number because it is formatted. But if column H is cleared then column I will clear if and only if it contains "Help!" & interior color red, so then both column H & column I cells will be blank.

 

[Peter_SSs]

But if in going the other way, in supposing that column H has a number and column I contains something other than "Help!" & interior red color then also do nothing.

But if going the other way, isn't column H now empty?

Anyway, if column H is either empty or contains a number as you seem to now indicate, try this

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range, c As Range
  
  Set Changed = Intersect(Target, Columns("H"))
  If Not Changed Is Nothing Then
    Application.EnableEvents = False
    For Each c In Changed
      With c
        If IsNumeric(.Value) And IsEmpty(.Offset(, 1).Value) Then
            With .Offset(, 1)
              .Value = "Help!"
              .Interior.Color = vbRed
            End With
        ElseIf IsEmpty(.Value) And .Offset(, 1).Value = "Help!" And .Offset(, 1).Interior.Color = vbRed Then
          .Offset(, 1).Clear
        End If
      End With
    Next c
    Application.EnableEvents = True
  End If
End Sub

 

[Pinaceous]

But if going the other way, isn't column H now empty?

Anyway, if column H is either empty or contains a number as you seem to now indicate, try this

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range, c As Range
 
  Set Changed = Intersect(Target, Columns("H"))
  If Not Changed Is Nothing Then
    Application.EnableEvents = False
    For Each c In Changed
      With c
        If IsNumeric(.Value) And IsEmpty(.Offset(, 1).Value) Then
            With .Offset(, 1)
              .Value = "Help!"
              .Interior.Color = vbRed
            End With
        ElseIf IsEmpty(.Value) And .Offset(, 1).Value = "Help!" And .Offset(, 1).Interior.Color = vbRed Then
          .Offset(, 1).Clear
        End If
      End With
    Next c
    Application.EnableEvents = True
  End If
End Sub

Hey

Peter_SSs!​

Many thanks, it works like a charm!

Respectfully,
pinaceous

 

[Peter_SSs]

Good news. Thanks for the confirmation.