How do I get this code to run only when certain text is entered in cells?

Ironman

Well-known Member
Joined
Jan 31, 2004
Messages
1,069
Office Version
  1. 365
Platform
  1. Windows
Hi

The below code runs in a worksheet_change event but I specifically only want it to run when the word "OTHER" is entered in Column B of the first filled row (same row as the one in the code) AND Column J of the same row begins with the text "Indoor bike session".
VBA Code:
Dim NextRow As Long
Application.EnableEvents = False
  If ActiveSheet.Name = "Training Log" Then
    NextRow = Range("A" & Rows.Count).End(xlUp).Row
    Range("A" & NextRow).Resize(, 6).Interior.Color = RGB(197, 217, 241)
    Range("I" & NextRow).Resize(, 2).Interior.Color = RGB(197, 217, 241)
    Range("I" & NextRow).Value = "Indoor bike session, 60 mins."
    Range("A" & NextRow).Select
  Else
    MsgBox "Cell fill does not work in this sheet", vbInformation, "Information"
  End If

lr = Range("A" & Rows.Count).End(xlUp).Row
If target.Address(0, 0) = Range("B" & lr).Address(0, 0) Then
    Range("F" & lr).Select
    MsgBox "Enter heart rate", vbInformation, "Indoor Bike Session Data"
Application.EnableEvents = True
End If

Hope you can help?

Many thanks!
 
Last edited:
Many thanks!

For some reason this part
VBA Code:
And Range("I" & target.Row).Value = "Indoor bike session, 60 mins."
was preventing it from running.

When I amended it to this
VBA Code:
'monitor column B for OTHER
If target.Column = 2 And target.Value = "OTHER" Then
    Range("A" & target.Row).Resize(, 6).Interior.Color = RGB(197, 217, 241)
    Range("I" & target.Row).Resize(, 2).Interior.Color = RGB(197, 217, 241)
    Range("I" & target.Row).Value = "Indoor bike session"
    'select cell col F
    Application.EnableEvents = False
    Range("F" & target.Row).Select
    Application.EnableEvents = True
    'display message
    MsgBox "Enter heart rate", vbInformation, "Indoor Bike Session Data"
End If
it all worked perfectly - I know it wasn't quite what I was originally looking for, but on reflection it actually doesn't matter, and it's absolutely fine!

Thank you so much for your time and trouble, that's really great!

@Michael M Thank you for your input too!
 
Upvote 0

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Guess I made a mistake interpreting #2 response in post 7.
Doesn't matter, glad you got it sorted.

I would suggest moving the Application.Enableevents=False line up to above the line writing to the cell
because there is a line higher up in the code setting Enableevents to True and I have no idea what happens between here and there
Rich (BB code):
    Range("I" & target.Row).Resize(, 2).Interior.Color = RGB(197, 217, 241)
    Application.EnableEvents = False
    Range("I" & target.Row).Value = "Indoor bike session"
    'select cell col F
    Range("F" & target.Row).Select
    Application.EnableEvents = True
 
Upvote 0
I'm sure you didn't, I'm a very confused person ?

Thanks for the additional info, I'll amend that.
 
Upvote 0

Forum statistics

Threads
1,223,914
Messages
6,175,351
Members
452,638
Latest member
Oluwabukunmi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top