Possible Conditional Formatting Workaroud?

[JoAnn]

I have conditional formatting already set to shade a row differently for 3
conditions. But I also need to have a date cell's value change to red if
it's manually changed.

I'm wondering if there's a way to use Excel's custom number format feature
to do this?

Here's the layout:
G - Submission Date (manually entered)
H - Hidden Workdays field to determine a 3 day Due Date date based on G
I - Displayed Due Date field that equals value of H
J - Hidden # Days Turnaround Field in General format (currently equals I -
G but can change that to H-G if I need to do that to make this work)

I want to have I's value display in RED if J<3 (or since they'd have to
manually change the displayed date, not sure if it's possible (or desirable)
to have the RED formatting dependant on the field being manually changed?).

Since I don't need to use I's value for calculations (can use H instead if
needed), can I use a custom number format that changes the manually entered
date to RED (I would be okay with it changing the format from mm/dd/yy to
mm-dd-yy if that was the only way to achieve the result -or even changing it
to text if needed).

If so ... what code do I need to do this?

Don't know if I would need to have the cell stay as a date field unless it's
changed & then apply the custom format that changes it to whatever works for
this? Or if it can all have the custom format and still look like the other
dates (mm/dd/yy).

The more I think about it, the more confused I get but I can't help thinking
it may be possible. I'm clearly in over my head here so I'd appreciate any
help anyone can give me with this.

Thank you all!

 

[JP]

If you wanted a VBA solution, you could use the Worksheet_Change Event
as a full replacement for conditional formatting. The code would be
placed in a sheet module (see http://www.rondebruin.nl/code.htm for
placement instructions).

For example, this code pops up a msgbox every time you edit a range,
giving you the range address.

Private Sub Worksheet_Change(ByVal Target As Range)

MsgBox "You edited cell(s) " & Target.Address & "."

End Sub


HTH,
JP