Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HOW DO YOU INSERT A MACRO INTO A FORMULA
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" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HOW DO YOU INSERT A MACRO INTO A FORMULA
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").Valu e If DateDiff("d", Date, Target) <= 30 Then ThisWorkbook.Worksheets("Sheet1").Range("A11").Fon t.Color = 255 End If End If ThisWorkbook.Worksheets("Sheet1").Range("A11").Act ivate 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HOW DO YOU INSERT A MACRO INTO A FORMULA
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").Valu e If DateDiff("d", Date, Target) <= 30 Then ThisWorkbook.Worksheets("Sheet1").Range("A11").Fon t.Color = 255 End If End If ThisWorkbook.Worksheets("Sheet1").Range("A11").Act ivate 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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HOW DO YOU INSERT A MACRO INTO A FORMULA
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").Valu e If DateDiff("d", Date, Target) <= 30 Then ThisWorkbook.Worksheets("Sheet1").Range("A11").Fon t.Color = 255 End If End If ThisWorkbook.Worksheets("Sheet1").Range("A11").Act ivate 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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HOW DO YOU INSERT A MACRO INTO A FORMULA
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").Valu e If DateDiff("d", Date, Target) <= 30 Then ThisWorkbook.Worksheets("Sheet1").Range("A11").Fon t.Color = 255 End If End If ThisWorkbook.Worksheets("Sheet1").Range("A11").Act ivate 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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HOW DO YOU INSERT A MACRO INTO A FORMULA
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").Valu e If DateDiff("d", Date, Target) <= 30 Then ThisWorkbook.Worksheets("Sheet1").Range("A11").Fon t.Color = 255 End If End If ThisWorkbook.Worksheets("Sheet1").Range("A11").Act ivate 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match then lookup | Excel Worksheet Functions | |||
INSERT NEW ROW MACRO BUTTON | Excel Discussion (Misc queries) | |||
Closing File Error | Excel Discussion (Misc queries) | |||
Insert New Row without shifting formula | Excel Worksheet Functions | |||
Formula for current month minus one = Quarter number in a macro. | Excel Discussion (Misc queries) |