software

I am trying to insert a macro based on a quot;Ifquot; 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 quot;targetquot; cell or you can write a
worksheet_change event macro. The first is by far the easiest.

--
Kind regards,

Niek Otten

quot;blopreste3180quot; gt; wrote in message
...
gt;I am trying to insert a macro based on a quot;Ifquot; formula that will highlight a
gt; specific cell or range of cells in a worksheet to a particular color. For
gt; example, if todays date is 30 days or less from a target date, then I want
gt; a
gt; name on a particular sheet to turn red to let me know to do something. Any
gt; 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(quot;Sheet1quot;).Range(quot;A2 quot;).Value) Then
Target = ThisWorkbook.Worksheets(quot;Sheet1quot;).Range(quot;A2quot;).Valu e
If DateDiff(quot;dquot;, Date, Target) lt;= 30 Then
ThisWorkbook.Worksheets(quot;Sheet1quot;).Range(quot;A11quot;).Fon t.Color = 255
End If
End If
ThisWorkbook.Worksheets(quot;Sheet1quot;).Range(quot;A11quot;).Act ivate
End Sub

A conditional format would work too.

Bill Horton

quot;blopreste3180quot; wrote:

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

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

quot;William Hortonquot; wrote:

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

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

quot;blopreste3180quot; wrote:

gt; I keep getting a Compile Error: Expected: = then it highlights the lt;= portion
gt; of your code. Any suggestions?
gt;
gt; quot;William Hortonquot; wrote:
gt;
gt; gt; Put this code in the worksheet section. It assumes the worksheet you are
gt; gt; working from is Sheet1, the target date is in cell A2, and the name that you
gt; gt; want the font to change is in cell A11. Make any adjustments to the code as
gt; gt; needed.
gt; gt;
gt; gt; Private Sub Worksheet_Activate()
gt; gt; Dim Target As Date
gt; gt; If IsDate(ThisWorkbook.Worksheets(quot;Sheet1quot;).Range(quot;A2 quot;).Value) Then
gt; gt; Target = ThisWorkbook.Worksheets(quot;Sheet1quot;).Range(quot;A2quot;).Valu e
gt; gt; If DateDiff(quot;dquot;, Date, Target) lt;= 30 Then
gt; gt; ThisWorkbook.Worksheets(quot;Sheet1quot;).Range(quot;A11quot;).Fon t.Color = 255
gt; gt; End If
gt; gt; End If
gt; gt; ThisWorkbook.Worksheets(quot;Sheet1quot;).Range(quot;A11quot;).Act ivate
gt; gt; End Sub
gt; gt;
gt; gt; A conditional format would work too.
gt; gt;
gt; gt; Bill Horton
gt; gt;
gt; gt; quot;blopreste3180quot; wrote:
gt; gt;
gt; gt; gt; I am trying to insert a macro based on a quot;Ifquot; formula that will highlight a
gt; gt; gt; specific cell or range of cells in a worksheet to a particular color. For
gt; gt; gt; example, if todays date is 30 days or less from a target date, then I want a
gt; gt; gt; name on a particular sheet to turn red to let me know to do something. Any
gt; gt; gt; 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.

quot;William Hortonquot; wrote:

gt; It's working on my machine. I'm not sure why it would highlite the lt;=. Does
gt; it give you any more information about the error? Is the entire line typed
gt; exactly as I had it?
gt;
gt; quot;blopreste3180quot; wrote:
gt;
gt; gt; I keep getting a Compile Error: Expected: = then it highlights the lt;= portion
gt; gt; of your code. Any suggestions?
gt; gt;
gt; gt; quot;William Hortonquot; wrote:
gt; gt;
gt; gt; gt; Put this code in the worksheet section. It assumes the worksheet you are
gt; gt; gt; working from is Sheet1, the target date is in cell A2, and the name that you
gt; gt; gt; want the font to change is in cell A11. Make any adjustments to the code as
gt; gt; gt; needed.
gt; gt; gt;
gt; gt; gt; Private Sub Worksheet_Activate()
gt; gt; gt; Dim Target As Date
gt; gt; gt; If IsDate(ThisWorkbook.Worksheets(quot;Sheet1quot;).Range(quot;A2 quot;).Value) Then
gt; gt; gt; Target = ThisWorkbook.Worksheets(quot;Sheet1quot;).Range(quot;A2quot;).Valu e
gt; gt; gt; If DateDiff(quot;dquot;, Date, Target) lt;= 30 Then
gt; gt; gt; ThisWorkbook.Worksheets(quot;Sheet1quot;).Range(quot;A11quot;).Fon t.Color = 255
gt; gt; gt; End If
gt; gt; gt; End If
gt; gt; gt; ThisWorkbook.Worksheets(quot;Sheet1quot;).Range(quot;A11quot;).Act ivate
gt; gt; gt; End Sub
gt; gt; gt;
gt; gt; gt; A conditional format would work too.
gt; gt; gt;
gt; gt; gt; Bill Horton
gt; gt; gt;
gt; gt; gt; quot;blopreste3180quot; wrote:
gt; gt; gt;
gt; gt; gt; gt; I am trying to insert a macro based on a quot;Ifquot; formula that will highlight a
gt; gt; gt; gt; specific cell or range of cells in a worksheet to a particular color. For
gt; gt; gt; gt; example, if todays date is 30 days or less from a target date, then I want a
gt; gt; gt; gt; name on a particular sheet to turn red to let me know to do something. Any
gt; gt; gt; gt; 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 quot;Sheet1quot;. A code window should appear on the top
right of the screen. Do the drop down menu that currently is showing
quot;generalquot; 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

quot;blopreste3180quot; wrote:

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