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

 

[My Aswer Is This]

Try this:
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

When you enter any value in column H the script will run:

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  10/4/2022  9:35:12 PM  EDT
If Target.Column = 8 Then
    Target.Offset(, 1).Value = "Help!"
    Target.Offset(, 1).Interior.Color = vbRed
End If
End Sub

 

[Pinaceous]

Try this:
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

When you enter any value in column H the script will run:

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  10/4/2022  9:35:12 PM  EDT
If Target.Column = 8 Then
    Target.Offset(, 1).Value = "Help!"
    Target.Offset(, 1).Interior.Color = vbRed
End If
End Sub

Wow, thank My Answer Is This! Please allow me to test it out and get back to you! Many thanks again! Pinaceous

 

[Pinaceous]

Hi MyAnswerIsThis,

Do you know in your code above, if you can specify its condition?

For example, if Column H has a number and Column I is blank then

If Target.Column = 8 Then
    Target.Offset(, 1).Value = "Help!"
    Target.Offset(, 1).Interior.Color = vbRed
End If

Please let me know.

Thank you!
pinaceous

 

[My Aswer Is This]

You said:
For example, if Column H has a number and Column I is blank then

If column "H" has what number?
and Column I is blank do what

 

[Peter_SSs]

Another option to try. This allows for the possibility of multiple values in column H being changed at the same time. It also prevents itself from being needlessly re-called when the text is entered into column I.

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
      If IsNumeric(c.Value) And IsEmpty(c.Offset(, 1).Value) Then
        With c.Offset(, 1)
          .Value = "Help!"
          .Interior.Color = vbRed
        End With
      End If
    Next c
    Application.EnableEvents = True
  End If
End Sub

Is it possible that after numbers are entered in column H, they could be removed or changed to some text value? If so, what do you want to happen to the red & "Help!"?
What about if column I has something other than "Help!" in it?

 

[My Aswer Is This]

Since your now getting help from others I will move on to help out on some other posting.

 

[Pinaceous]

Hey Peter_SSs!

Thank you for your intuitive thinking!

I'm going to test it out right now!

Respectfully,
pinaceous

 

[Pinaceous]

Hi Peter_SSs,

Actually, thank you for your questions! Can you add an additional criteria onto your code; if the Column H entry gets deleted that the Column I interior color & text will get removed?

For example; if the first picture represents after the code is executed, the second picture represents what I propose will happen when the Column H entry gets deleted.

Is this possible? Many thanks!
pinaceous

 

[Peter_SSs]

Is this possible?

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

1.
You earlier said ..

if Column H has a number and Column I is blank then

.. 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?