Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
***Cell Shading (Fill Color)***
Hi all,
I need some help. I'm looking for a way to do this. I have columns with formulas. And sometimes those formulas in a particular cell or 2 are overwritten with a "hard" number. Is it possible to somehow make it so that when someone changes the cell to a number that it turns the cell yellow automatically? Please help. Thank you!! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
***Cell Shading (Fill Color)***
Why not just train people not to do that?
You could write some complicated VBA code that checks the content of a cell to see if it contains hard coded values. In the Worksheet_Change Event, it could act as a realtime monitor. There are also some spreadsheet auditing programs that can do this for you. Check out: http://arxiv.org/ftp/arxiv/papers/0803/0803.0169.pdf HTH, JP On Jul 14, 2:55*pm, Steve wrote: Hi all, I need some help. *I'm looking for a way to do this. *I have columns with formulas. *And sometimes those formulas in a particular cell or 2 are overwritten with a "hard" number. *Is it possible to somehow make it so that when someone changes the cell to a number that it turns the cell yellow automatically? Please help. Thank you!! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
***Cell Shading (Fill Color)***
You can let your users type away and still prevent them from changing the
cells a specified range. For example... Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 4 Then On Error GoTo Done Application.EnableEvents = False Application.Undo End If Done: Application.EnableEvents = True End Sub will prevent the user from making a change to anything (formulas or constants) in Column D. Simply change the If-Then test to cover whatever range you need it to cover. Rick "Steve" wrote in message ... Hi all, I need some help. I'm looking for a way to do this. I have columns with formulas. And sometimes those formulas in a particular cell or 2 are overwritten with a "hard" number. Is it possible to somehow make it so that when someone changes the cell to a number that it turns the cell yellow automatically? Please help. Thank you!! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
***Cell Shading (Fill Color)***
I would have thought the most obvious thing to do, if user should not change
the cell at all, is to protect the sheet with those cells locked. However if user is allowed to amend formulas you could use Conditional formats. First select cell A1 ( * important * ) Ctrl-F3, Define name Name: NotFormula Refersto: =LEFT(GET.CELL(41,A1),1)<"=" Select your Formula cells you want coloured if they are changed to a non formula, ie do not start with an "=" Format, Conditional formats, select "Formula Is" in the left dropdown and this formula =NotFormula (I'm sure there must be some simpler function to indicate if the cell is a formula, but off the top of my head I can't think what it might be). Regards, Peter T "Steve" wrote in message ... Hi all, I need some help. I'm looking for a way to do this. I have columns with formulas. And sometimes those formulas in a particular cell or 2 are overwritten with a "hard" number. Is it possible to somehow make it so that when someone changes the cell to a number that it turns the cell yellow automatically? Please help. Thank you!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count if a cell has color shading | Excel Discussion (Misc queries) | |||
Cell shading reverts to No Fill | Excel Discussion (Misc queries) | |||
Cell Color Shading Changing | Excel Discussion (Misc queries) | |||
How to add fill colors (cell shading) in EXCEL? | Excel Discussion (Misc queries) | |||
cell color shading | Excel Discussion (Misc queries) |