HOW DO YOU INSERT A MACRO INTO A FORMULA

I am trying to insert a macro based on a "If" formula that will highlight a
specific cell or range of cells in a worksheet to a particular color. For
example, if todays date is 30 days or less from a target date, then I want a
name on a particular sheet to turn red to let me know to do something. Any
help is appreciated.

You cannot execute a macro from a formula.
You can use conditional formatting for the "target" cell or you can write a
worksheet_change event macro. The first is by far the easiest.

--
Kind regards,

Niek Otten

"blopreste3180" <blopreste3180@discussions.microsoft.com> wrote in message
news:540DBC2E-807C-4562-9F2D-F46374D925AD@microsoft.com...
>I am trying to insert a macro based on a "If" formula that will highlight a
> specific cell or range of cells in a worksheet to a particular color. For
> example, if todays date is 30 days or less from a target date, then I want
> a
> name on a particular sheet to turn red to let me know to do something. Any
> help is appreciated.

Put this code in the worksheet section. It assumes the worksheet you are
working from is Sheet1, the target date is in cell A2, and the name that you
want the font to change is in cell A11. Make any adjustments to the code as
needed.

Private Sub Worksheet_Activate()
Dim Target As Date
If IsDate(ThisWorkbook.Worksheets("Sheet1").Range("A2").Value) Then
Target = ThisWorkbook.Worksheets("Sheet1").Range("A2").Value
If DateDiff("d", Date, Target) <= 30 Then
ThisWorkbook.Worksheets("Sheet1").Range("A11").Font.Color = 255
End If
End If
ThisWorkbook.Worksheets("Sheet1").Range("A11").Activate
End Sub

A conditional format would work too.

Bill Horton

"blopreste3180" wrote:

> I am trying to insert a macro based on a "If" formula that will highlight a
> specific cell or range of cells in a worksheet to a particular color. For
> example, if todays date is 30 days or less from a target date, then I want a
> name on a particular sheet to turn red to let me know to do something. Any
> help is appreciated.

I keep getting a Compile Error: Expected: = then it highlights the <= portion
of your code. Any suggestions?

"William Horton" wrote:

> Put this code in the worksheet section. It assumes the worksheet you are
> working from is Sheet1, the target date is in cell A2, and the name that you
> want the font to change is in cell A11. Make any adjustments to the code as
> needed.
>
> Private Sub Worksheet_Activate()
> Dim Target As Date
> If IsDate(ThisWorkbook.Worksheets("Sheet1").Range("A2").Value) Then
> Target = ThisWorkbook.Worksheets("Sheet1").Range("A2").Value
> If DateDiff("d", Date, Target) <= 30 Then
> ThisWorkbook.Worksheets("Sheet1").Range("A11").Font.Color = 255
> End If
> End If
> ThisWorkbook.Worksheets("Sheet1").Range("A11").Activate
> End Sub
>
> A conditional format would work too.
>
> Bill Horton
>
> "blopreste3180" wrote:
>
> > I am trying to insert a macro based on a "If" formula that will highlight a
> > specific cell or range of cells in a worksheet to a particular color. For
> > example, if todays date is 30 days or less from a target date, then I want a
> > name on a particular sheet to turn red to let me know to do something. Any
> > help is appreciated.

It's working on my machine. I'm not sure why it would highlite the <=. Does
it give you any more information about the error? Is the entire line typed
exactly as I had it?

"blopreste3180" wrote:

> I keep getting a Compile Error: Expected: = then it highlights the <= portion
> of your code. Any suggestions?
>
> "William Horton" wrote:
>
> > Put this code in the worksheet section. It assumes the worksheet you are
> > working from is Sheet1, the target date is in cell A2, and the name that you
> > want the font to change is in cell A11. Make any adjustments to the code as
> > needed.
> >
> > Private Sub Worksheet_Activate()
> > Dim Target As Date
> > If IsDate(ThisWorkbook.Worksheets("Sheet1").Range("A2").Value) Then
> > Target = ThisWorkbook.Worksheets("Sheet1").Range("A2").Value
> > If DateDiff("d", Date, Target) <= 30 Then
> > ThisWorkbook.Worksheets("Sheet1").Range("A11").Font.Color = 255
> > End If
> > End If
> > ThisWorkbook.Worksheets("Sheet1").Range("A11").Activate
> > End Sub
> >
> > A conditional format would work too.
> >
> > Bill Horton
> >
> > "blopreste3180" wrote:
> >
> > > I am trying to insert a macro based on a "If" formula that will highlight a
> > > specific cell or range of cells in a worksheet to a particular color. For
> > > example, if todays date is 30 days or less from a target date, then I want a
> > > name on a particular sheet to turn red to let me know to do something. Any
> > > help is appreciated.

Yes, I copied and pasted what you had provided. Be aware however, that I am
not sure how to get this to run in my spreadsheet. Is this entered into the
VBA program and then run? Sorry, I am very new to this so any basic
information you can give me on how to get this to work would be appreciated.

"William Horton" wrote:

> It's working on my machine. I'm not sure why it would highlite the <=. Does
> it give you any more information about the error? Is the entire line typed
> exactly as I had it?
>
> "blopreste3180" wrote:
>
> > I keep getting a Compile Error: Expected: = then it highlights the <= portion
> > of your code. Any suggestions?
> >
> > "William Horton" wrote:
> >
> > > Put this code in the worksheet section. It assumes the worksheet you are
> > > working from is Sheet1, the target date is in cell A2, and the name that you
> > > want the font to change is in cell A11. Make any adjustments to the code as
> > > needed.
> > >
> > > Private Sub Worksheet_Activate()
> > > Dim Target As Date
> > > If IsDate(ThisWorkbook.Worksheets("Sheet1").Range("A2").Value) Then
> > > Target = ThisWorkbook.Worksheets("Sheet1").Range("A2").Value
> > > If DateDiff("d", Date, Target) <= 30 Then
> > > ThisWorkbook.Worksheets("Sheet1").Range("A11").Font.Color = 255
> > > End If
> > > End If
> > > ThisWorkbook.Worksheets("Sheet1").Range("A11").Activate
> > > End Sub
> > >
> > > A conditional format would work too.
> > >
> > > Bill Horton
> > >
> > > "blopreste3180" wrote:
> > >
> > > > I am trying to insert a macro based on a "If" formula that will highlight a
> > > > specific cell or range of cells in a worksheet to a particular color. For
> > > > example, if todays date is 30 days or less from a target date, then I want a
> > > > name on a particular sheet to turn red to let me know to do something. Any
> > > > help is appreciated.

Open the Excel file. Hit Alt-F11. that should open the VBA editor. On the
left hand side of the screen (Project - VBA Project) locate the Excel file
and then double click on "Sheet1". A code window should appear on the top
right of the screen. Do the drop down menu that currently is showing
"general" and choose worksheet instead. Find a blank area and paste the code
in. This code is part of the worksheet activate event which means each time
the worksheet is activated the macro will run. That means if you open the
workbook and go to sheet1 the macro will run. If you then go to a different
sheet and then back to sheet1 the macro will run.

Bill Horton

"blopreste3180" wrote:

> Yes, I copied and pasted what you had provided. Be aware however, that I am
> not sure how to get this to run in my spreadsheet. Is this entered into the
> VBA program and then run? Sorry, I am very new to this so any basic
> information you can give me on how to get this to work would be appreciated.
>
> "William Horton" wrote:
>
> > It's working on my machine. I'm not sure why it would highlite the <=. Does
> > it give you any more information about the error? Is the entire line typed
> > exactly as I had it?
> >
> > "blopreste3180" wrote:
> >
> > > I keep getting a Compile Error: Expected: = then it highlights the <= portion
> > > of your code. Any suggestions?
> > >
> > > "William Horton" wrote:
> > >
> > > > Put this code in the worksheet section. It assumes the worksheet you are
> > > > working from is Sheet1, the target date is in cell A2, and the name that you
> > > > want the font to change is in cell A11. Make any adjustments to the code as
> > > > needed.
> > > >
> > > > Private Sub Worksheet_Activate()
> > > > Dim Target As Date
> > > > If IsDate(ThisWorkbook.Worksheets("Sheet1").Range("A2").Value) Then
> > > > Target = ThisWorkbook.Worksheets("Sheet1").Range("A2").Value
> > > > If DateDiff("d", Date, Target) <= 30 Then
> > > > ThisWorkbook.Worksheets("Sheet1").Range("A11").Font.Color = 255
> > > > End If
> > > > End If
> > > > ThisWorkbook.Worksheets("Sheet1").Range("A11").Activate
> > > > End Sub
> > > >
> > > > A conditional format would work too.
> > > >
> > > > Bill Horton
> > > >
> > > > "blopreste3180" wrote:
> > > >
> > > > > I am trying to insert a macro based on a "If" formula that will highlight a
> > > > > specific cell or range of cells in a worksheet to a particular color. For
> > > > > example, if todays date is 30 days or less from a target date, then I want a
> > > > > name on a particular sheet to turn red to let me know to do something. Any
> > > > > help is appreciated.