Conditional format problem

[Guest]

I have a column of data in an excel spreadsheet that uses the "workday"
function to create a cell value that shows a date. when you manually input a
date in one of the cells, all of the cells below auto adjust to the new dates
based on the "workday" formula. what I want to do is when a date is manually
input into one of the cells that has the workday formula in it, I want the
cell to change color if the new date is before or after the date that was
originally in this cell with the formula. when I try to input the conditional
format, I am getting an error message that I cannot refer to other worksheets
or workbooks, but all of the information is only on 1 worksheet and the
formula in the conditional format refers to that same worksheet.
please help as my Brain has frozen.
thanks.
Phil.

 

[Don Guillett]

You could do this with a dummy duplicate and hidden colunmn to the right and
this worksheet change event.
Right click sheet tab>view code>insert this>modify to suit>SAVE

Private Sub Worksheet_change(ByVal Target As Range)
If Target.Row < 2 Or Target.Column <> 3 Then Exit Sub
If Target <> Target.Offset(, 1) Then Target.Interior.ColorIndex = 4
End Sub

 

[Bob Phillips]

Another way

Private PrevValue

Private Sub Worksheet_Activate()
PrevValue = ActiveCell.Value
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("H1:H10")) Is Nothing Then
With Target
If .Value <> PrevValue Then
.Interior.ColorIndex = 3
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
PrevValue = Target.Value
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.



--

HTH

RP
(remove nothere from the email address if mailing direct)