Format help when not using formula

**Ringger** · 09-14-2009, 11:49 AM

I have a spreadsheet (excel 2007) where there are lots of formulas (dates). Sometimes, I need to "hard key" a date, breaking the formula. Is it possible to conditional format a cell when the formula is not used the cell changes color?

**DonkeyOte** · 09-14-2009, 12:56 PM

You would need to use VBA to automate this, is that viable ? If so...

What is the range of interest ?
(ie the range of cells that will contain either dates via formulae or manual dates)

**Ringger** · 09-14-2009, 01:12 PM

I am not familiar w/VBA. How my spreadsheet is set up, I input into 1 cell a specific date....the rest of the cells are calcuated based upon the formula used ex: =WORKDAY(AK329,-AH$18,Holidays). There are appx 27 columns and 350 rows that have this formula. At times I override the formula with a specific date and use the spreadsheet for scheduling. What I'm wanting to see is if I override the formula, the cell changes color so I can see it in a quick glance. The reason want to see at a glance, sometimes the wrong dates are entered, changed, etc...and I cannot see what has been changed to a hard date vs. using the formula. I currently do this manually, but sometimes others maintain the spreadsheet and they may not change the color. I thought there might be a conditional format for when not using a formula, as you can conditional format based on a specific formula...thought it might work the opposite way. Does this make more sense?

**DonkeyOte** · 09-14-2009, 01:17 PM

You have 2 choices as I see it

1
as and when you wish to audit you can highlight all date cells and set to some default format, then press F5 -> Special -> Constants -> Numbers -> OK you can format all manually entered dates simultaneously.

2
use VBA Worksheet_Change event such that whenever a cell within your data range is updated it will set format accordingly

It may be that you find option 1 to be both viable and preferable.

**martindwilson** · 09-14-2009, 01:54 PM

you could with more functions add in
http://download.cnet.com/Morefunc/30...-10423159.html
use
=NOT(XLM.GET.CELL(48,Sheet1!B9)) but you'd have to assign it to a name
insert/ name put a name , say myname, in and in the refers to box put
=NOT(XLM.GET.CELL(48,Sheet1!B9)) where A1 is the first cell you want to format
then in cf use formula is =myname
you can then copy that and paste special formats to anywhere you want
once formula is deleted or overwritten cell colour will change