Value change change cell color 1

[sofiavba]

Hi everyone,

First off I just wanted to say that this is GREAT, I have gotten so much good advice that I cant thank everyone enough.

Ok here is the problem, I have a cell on my spreadsheet, which if empty when the sheet is opened. I want the cell to change its color when a user goes in and enters a number. The cell also has a formula in it, if the formula is used the cell's color should not changed, only when a user selects the cell and changes the value should the color change.I cant use the conditional formatting because it just will not do the trick.

Any ideas?

 

[SkipVought]

So when the user change the value, the formula is destroyed.

Is that your design?

What happens the next time when there's no formula?



Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[sofiavba]

If the users enters a value we do nothing actually, the design at this point is to allow the user full access to all fields and formulas can be distroyed if needed.

But we do want to know if the formula was replaced by a user entering data.

 

[SkipVought]

The use the Worksheet_Change event (right click the sheet tab and select View Code).

If the Target address equals the cell address, then your case is true. Paste this code and modify as needed for your cell reference and colorindex.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = [a1].Address Then target.interior.colorindex = 5
End Sub

use you macro recorder to find a colorindex that suits you.

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[sofiavba]

Thanks Skip.