VBA check if a specific value has been entered into range

[HarveyHirst1]

Hello All,

Thanks for your time in reading this!

I'm new to VBA so please bear with me .
I have recently started to develop new processes at work for operatives in the factory to fill out.

My end game is a piece of code which looks to see if a "Yes" has been entered into a range of cells. If so, it will trigger another Macro which sends a email and opens a new document.

It is nearly there, but, i can't get the code to work that will trigger the Macros when a "Yes" has been entered into a range.

I made this code;

Sub Worksheet_Change(ByVal Target As Range)
    Dim WatchRange As Range
    Dim IntersectRange As Range
    Set WatchRange = Range("O3:O4993")
    Set IntersectRange = Intersect(Target, WatchRange)
    If IntersectRange = "Yes" Then
        Call NewFMRAutoEmail
    End If
End Sub

The code works in the O column, like i want it to. i.e. if i Enter Yes into O3 it runs my Macro "NewFMRAutoEmail".
However if I enter anything else in any other column i.e. A3 i get "Run-time error '91 .. Object variable or With block variable not set".

What am i doing wrong?

Thanks in advance for the help.

[mdmackillop]

The ranges don't intersect so you get an error looking for Yes

Sub Worksheet_Change(ByVal Target As Range)
    Dim WatchRange As Range
    Dim IntersectRange As Range
    If Target.Cells.Count > 1 Then Exit Sub
    Set WatchRange = Range("O3:O4993")
    Set IntersectRange = Intersect(Target, WatchRange)
    If Not (IntersectRange Is Nothing) Then
        If UCase(IntersectRange) = "YES" Then
            Call NewFMRAutoEmail
        End If
    End If
End Sub

[HarveyHirst1]

This works perfectly mdmackillop! Thank you!

[Lotlawyer]

Hello all

I am trying to use this code to remove Weekends from an Excel Calendar, but I keep getting errors. Anyone????

Sub HideColumns()
Dim ws As Worksheet
Dim Cell As Range
For Each ws In This
Workbook.Sheets
For Each Cell In Range("A4:Z4")
If Cell.Value = "Sat" Or Cell.Value = "Sun" Then Cell EntireColumn.Hidden = True
End If
Next Cell
Next ws
End Sub