Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Conditional Formatting to write Text Value only if true
I have a calendar set up so that I enter one "start date" and it will
generate an 8-week schedule. I can then enter work times in for each day for each employee. What I want to do is use the following formula in a conditional format =IF(AND(ABS(S14-$B$1)<55)*((S14-$B$1)-1), "H", "") to fill in "H" (and then "O", "L", "I", "D", "A", "Y") but if the condition is not true allow the user of the spreadsheet to fill in the hours the employee will work that day. In the above formula B1 is the start date. S14 is one of the Federal Holidays. The above tests if it is within the 8 week range. It correctly prints "H" when I enter it in as a regular formula, but doesn't seem to print the "H" when I enter it in as a conditional formula. I'm using Excel 2000 and am stuck with that, because my workplace won't allow any upgrades. To sum up, can I use conditional formatting in Excel 2000 to write a text value in a cell? I don't want the user to have to enter the hours worked into a conditional formatting statement, but have them enter in the hours and have those overwritten by a conditional statment if that date falls on a holiday. -- Jim S |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Conditional Formatting to write Text Value only if true
On Apr 6, 8:15*pm, Jim S wrote:
I have a calendar set up so that I enter one "start date" and it will generate an 8-week schedule. *I can then enter work times in for each day for each employee. What I want to do is use the following formula in a conditional format =IF(AND(ABS(S14-$B$1)<55)*((S14-$B$1)-1), "H", "") to fill in "H" (and then "O", "L", "I", "D", "A", "Y") *but if the condition is not true allow the user of the spreadsheet to fill in the hours the employee will work that day. In the above formula B1 is the start date. *S14 is one of the Federal Holidays. The above tests if it is within the 8 week range. *It correctly prints "H" when I enter it in as a regular formula, but doesn't seem to print the "H" when I enter it in as a conditional formula. *I'm using Excel 2000 and am stuck with that, because my workplace won't allow any upgrades. To sum up, can I use conditional formatting in Excel 2000 to write a text value in a cell? *I don't want the user to have to enter the hours worked into a conditional formatting statement, but have them enter in the hours and have those overwritten by a conditional statment if that date falls on a holiday. -- Jim S Hi Jim, Short answer is 'No'. Conditional formatting can only change the visual display elements of the cell, not the actual contents. The only way to do what you are asking is via a macro that fires on the Worksheet_Change event. I.e. write some code that tests what the user enters (or deletes or doesn't enter) and then performs what you want. I'm out of time tonight, but if someone hasn't given a sample by tomorrow I'll see what I can do for you. Cheers, Ivan. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Conditional Formatting to write Text Value only if true
Thanks, Ivan.
If you have time to show me, I'd appreciate it. At least I know that trying to input text values through conditional formatting won't work. Thanks again, I really appreciate your taking the time to let me know, Jim -- Jim S "Ivyleaf" wrote: On Apr 6, 8:15 pm, Jim S wrote: I have a calendar set up so that I enter one "start date" and it will generate an 8-week schedule. I can then enter work times in for each day for each employee. What I want to do is use the following formula in a conditional format =IF(AND(ABS(S14-$B$1)<55)*((S14-$B$1)-1), "H", "") to fill in "H" (and then "O", "L", "I", "D", "A", "Y") but if the condition is not true allow the user of the spreadsheet to fill in the hours the employee will work that day. In the above formula B1 is the start date. S14 is one of the Federal Holidays. The above tests if it is within the 8 week range. It correctly prints "H" when I enter it in as a regular formula, but doesn't seem to print the "H" when I enter it in as a conditional formula. I'm using Excel 2000 and am stuck with that, because my workplace won't allow any upgrades. To sum up, can I use conditional formatting in Excel 2000 to write a text value in a cell? I don't want the user to have to enter the hours worked into a conditional formatting statement, but have them enter in the hours and have those overwritten by a conditional statment if that date falls on a holiday. -- Jim S Hi Jim, Short answer is 'No'. Conditional formatting can only change the visual display elements of the cell, not the actual contents. The only way to do what you are asking is via a macro that fires on the Worksheet_Change event. I.e. write some code that tests what the user enters (or deletes or doesn't enter) and then performs what you want. I'm out of time tonight, but if someone hasn't given a sample by tomorrow I'll see what I can do for you. Cheers, Ivan. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Conditional Formatting to write Text Value only if true
On Apr 7, 7:37*am, Jim S wrote:
Thanks, Ivan. If you have time to show me, I'd appreciate it. At least I know that trying to input text values through conditional formatting won't work. *Thanks again, I really appreciate your taking the time to let me know, Jim -- Jim S "Ivyleaf" wrote: On Apr 6, 8:15 pm, Jim S wrote: I have a calendar set up so that I enter one "start date" and it will generate an 8-week schedule. *I can then enter work times in for each day for each employee. What I want to do is use the following formula in a conditional format =IF(AND(ABS(S14-$B$1)<55)*((S14-$B$1)-1), "H", "") to fill in "H" (and then "O", "L", "I", "D", "A", "Y") *but if the condition is not true allow the user of the spreadsheet to fill in the hours the employee will work that day. In the above formula B1 is the start date. *S14 is one of the Federal Holidays. The above tests if it is within the 8 week range. *It correctly prints "H" when I enter it in as a regular formula, but doesn't seem to print the "H" when I enter it in as a conditional formula. *I'm using Excel 2000 and am stuck with that, because my workplace won't allow any upgrades. To sum up, can I use conditional formatting in Excel 2000 to write a text value in a cell? *I don't want the user to have to enter the hours worked into a conditional formatting statement, but have them enter in the hours and have those overwritten by a conditional statment if that date falls on a holiday. -- Jim S Hi Jim, Short answer is 'No'. Conditional formatting can only change the visual display elements of the cell, not the actual contents. The only way to do what you are asking is via a macro that fires on the Worksheet_Change event. I.e. write some code that tests what the user enters (or deletes or doesn't enter) and then performs what you want. I'm out of time tonight, but if someone hasn't given a sample by tomorrow I'll see what I can do for you. Cheers, Ivan.- Hide quoted text - - Show quoted text - Hi Jim, Please don't think of the following code as a final solution, because I know it isn't what you are after. However, it's as good as I can do at this stage without knowing a bit more about exactly what you want to achieve: Private Sub Worksheet_Change(ByVal Target As Range) Dim ChngRng As Range, HotRng As Range Dim StartDate As Date, Enddate As Date, HolDate As Date Set HotRng = Range("$C$5:$C$200") Set ChngRng = Intersect(Target, HotRng) If Not ChngRng Is Nothing Then StartDate = Range("$B$1") Enddate = DateAdd("ww", 8, StartDate) HolDate = Range("$S$14") For Each cell In HotRng.Cells If HolDate = StartDate And HolDate < Enddate Then cell = "H" End If Next End If End Sub What this does is as follows: If someone changes a cell in the range C5:C200 (just a made up range), the code will loop through all the cells in the input range to check their integrity. If there is a date in cell S14 that falls between B1 and B1+8 weeks, then the macro will overwrite the value in the cell and replace it with "H". As it is, the code is useless since it will put "H" in every cell in the 'Hot range' regardless of the corresponding date, if there is a date in S14 that falls between the start and end dates. However, I am hoping that it still might help you to comprehend the process and maybe you can post back with more detail and I can re-work to make it more useful. It would be handy to know what the layout of your input cells are etc for me to understand better what you are trying to do. I would actually think it would be better to loop through the range, looking at the date of each cell and then using a find method on your 'holiday' range to see if the date is a holiday, but I'll wait to hear back from you. Cheers, Ivan. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Conditional Formatting to write Text Value only if true
Again, I can't thank you enough.
This opens a whole new world to me since I've not done a macro in Excel before. I am, however, familiar with simple programming and will now be able to take this and modify it I think. I've got to head to my work now, but tonight I will try and see what I can do and post back if I can figure it out. Thanks, Ivan! Jim -- Jim S "Ivyleaf" wrote: On Apr 7, 7:37 am, Jim S wrote: Thanks, Ivan. If you have time to show me, I'd appreciate it. At least I know that trying to input text values through conditional formatting won't work. Thanks again, I really appreciate your taking the time to let me know, Jim -- Jim S "Ivyleaf" wrote: On Apr 6, 8:15 pm, Jim S wrote: I have a calendar set up so that I enter one "start date" and it will generate an 8-week schedule. I can then enter work times in for each day for each employee. What I want to do is use the following formula in a conditional format =IF(AND(ABS(S14-$B$1)<55)*((S14-$B$1)-1), "H", "") to fill in "H" (and then "O", "L", "I", "D", "A", "Y") but if the condition is not true allow the user of the spreadsheet to fill in the hours the employee will work that day. In the above formula B1 is the start date. S14 is one of the Federal Holidays. The above tests if it is within the 8 week range. It correctly prints "H" when I enter it in as a regular formula, but doesn't seem to print the "H" when I enter it in as a conditional formula. I'm using Excel 2000 and am stuck with that, because my workplace won't allow any upgrades. To sum up, can I use conditional formatting in Excel 2000 to write a text value in a cell? I don't want the user to have to enter the hours worked into a conditional formatting statement, but have them enter in the hours and have those overwritten by a conditional statment if that date falls on a holiday. -- Jim S Hi Jim, Short answer is 'No'. Conditional formatting can only change the visual display elements of the cell, not the actual contents. The only way to do what you are asking is via a macro that fires on the Worksheet_Change event. I.e. write some code that tests what the user enters (or deletes or doesn't enter) and then performs what you want. I'm out of time tonight, but if someone hasn't given a sample by tomorrow I'll see what I can do for you. Cheers, Ivan.- Hide quoted text - - Show quoted text - Hi Jim, Please don't think of the following code as a final solution, because I know it isn't what you are after. However, it's as good as I can do at this stage without knowing a bit more about exactly what you want to achieve: Private Sub Worksheet_Change(ByVal Target As Range) Dim ChngRng As Range, HotRng As Range Dim StartDate As Date, Enddate As Date, HolDate As Date Set HotRng = Range("$C$5:$C$200") Set ChngRng = Intersect(Target, HotRng) If Not ChngRng Is Nothing Then StartDate = Range("$B$1") Enddate = DateAdd("ww", 8, StartDate) HolDate = Range("$S$14") For Each cell In HotRng.Cells If HolDate = StartDate And HolDate < Enddate Then cell = "H" End If Next End If End Sub What this does is as follows: If someone changes a cell in the range C5:C200 (just a made up range), the code will loop through all the cells in the input range to check their integrity. If there is a date in cell S14 that falls between B1 and B1+8 weeks, then the macro will overwrite the value in the cell and replace it with "H". As it is, the code is useless since it will put "H" in every cell in the 'Hot range' regardless of the corresponding date, if there is a date in S14 that falls between the start and end dates. However, I am hoping that it still might help you to comprehend the process and maybe you can post back with more detail and I can re-work to make it more useful. It would be handy to know what the layout of your input cells are etc for me to understand better what you are trying to do. I would actually think it would be better to loop through the range, looking at the date of each cell and then using a find method on your 'holiday' range to see if the date is a holiday, but I'll wait to hear back from you. Cheers, Ivan. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Write a formula for a penalty for status of TRUE? | Excel Worksheet Functions | |||
Conditional Formatting Applied when Statement Not True | Excel Discussion (Misc queries) | |||
Conditional Formatting for True/False | Excel Programming | |||
If B1 = 0 then D1= True - I need to write this in a macro | Excel Programming | |||
comparing data and write values if true | Excel Programming |