CODEWITHSUNDEEP
vba
Henk Straten
Henk Straten

Reputation: 1445

Trigger a macro based on a certain value in a cell

I have a macro that triggered by a button and then colours a certrain range. So like:

Sub colourRange()
   #Colour range
end sub

This all works but thing is that i have to push a button for it to work. What I am actually looking is some kind of if / else statement that triggers a macro when you fill in a certain value in a cell.

Can anbybody tell me if there is a way to trigger a macro by typing a certain value in a cell?

Upvotes: 1

Views: 259

Answers (2)

user3598756
user3598756

Reputation: 29421

from

triggers a macro when you fill in a certain value in a cell

I'd say that you'd want to react to any cell changing provided it has a been entered a certain value

then I'd code in the relevant worksheet code pane what follows:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.count>1 And Target.Value = "your certain value" Then colourRange
End Sub

If you want that to happen for every worksheet, then code it in ThisWorkbook code pane as follows:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Target.Count > 1 And Target.Value = "your certain value" Then colourRange
End Sub

Upvotes: 1

A.S.H
A.S.H

Reputation: 29352

Your first (natural) choice should be conditional formatting. If it doesnt work for your specific case, you can intercept the Worksheet_Change event:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, myRange) Is Nothing And myRange.Value = ACertainValue Then
        Call colourRange
    End If
End Sub

Put the above handler in the code module of the Worksheet that you want to monitor.

Upvotes: 2

Related Questions