Using Conditional Formatting to write Text Value only if true

[Jim S]

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.

 

[Ivyleaf]

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.

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.

 

[Jim S]

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

 

[Ivyleaf]

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








- 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.

 

[Jim S]

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