Highlight a cell when a user overrides its formula

Could you do something like this:

if not target = your formula then

Change the color

end if

Do this using the change sub routine for the sheet. I guess you would have to have an if statment to continue only on the cells you have formulas on.

Does this help? I am new to this.

Brian

Assume your column of formulas is column A, Then place this code on the Sheet Module.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
Target.Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
End If

End Sub

This will color the cell Yellow when it is changed.

Juan Pablo G posted an interesting way of handling this on Dec 18.
1. Select A1
2. Go to Insert, Names, Define.
3. Put a name in it, like HASFORM
4. In the refers line put:
5. GET.CELL(48,A1)
6. Select your formula column
7. Select conditional formatting, change Value to Formula is, and put this formula:
=HASFORM
9. Click on the pattern tab and select say pale green

Because all your cells have formulas they will be pale green. If you overtype them the green will disappear.
(Don't thank me, thank Juan)
Derek

Assume your column of formulas is column A, Then place this code on the Sheet Module. If Target.Column = 1 Then Target.Select With Selection.Interior .ColorIndex = 6 .Pattern = xlSolid End With End If End Sub

: I have a time line worksheet in which there is a single column that has formulas its length. I want to know if there is a way to "conditionally format" these cells so that when the user overrides the formula and types in a value, the cell's colour is changed. This is different from "Track Changes" throughout the workbook, something I do not want to do...

This works well, but now let's extend this somehow...how do I get it to exhibit the same behaviour over multiple worksheets?

1. Select A1 2. Go to Insert, Names, Define. 3. Put a name in it, like HASFORM 4. In the refers line put: 5. GET.CELL(48,A1) 6. Select your formula column 7. Select conditional formatting, change Value to Formula is, and put this formula: =HASFORM 9. Click on the pattern tab and select say pale green (Don't thank me, thank Juan) Derek

: I have a time line worksheet in which there is a single column that has formulas its length. I want to know if there is a way to "conditionally format" these cells so that when the user overrides the formula and types in a value, the cell's colour is changed. This is different from "Track Changes" throughout the workbook, something I do not want to do...

I did not get this to work...is there something I don't understand about the get.cell(48,A1)? I typed this in, but when I went back to recheck the name, it placed quotes around it...is that a problem? I am using Excel 97. Likewise, I am not sure what the 48 refers to...maybe this is my problem? Is the column to be affected, by this formula, column A?