Override cell value with conditional format

**craigproudfoot** · 01-25-2010, 03:18 PM

Hi
This one has me stumped.
I have attached a sample of the spreadsheet to help clarify what I’m after.
If cell B11 ='CASH' then cell C11 will turn green (conditional format), but I would like the value to change to a 'YES' (but I still want to have the option of the drop down list)
The problem is, I still want the cell to operate as a drop down list, and so if you change the value of cell B11 later, you have the option to change the value of C11 via the drop down list.
To explain the principle of what I am doing, the spreadsheet is a personal track of monthly receipts which are then checked against bank statements.
When you enter a receipt amount, you also specify which card was used to make the purchase. (B11)
Once the bank statement comes in, then C11 (which starts off as a red fill with an ‘X’ symbol) can be changed to ‘YES’ to show that the receipt has been checked off against the bank statement.
If however when you enter the receipt and it is ‘CASH’ in B11, then C11 should automatically change to green (which it currently does), but also to ‘YES’ – as any cash transactions won’t need to be checked against a bank statement.
Also, it would be neater if C11 remained blank until B10 had a value in it. Once B10 has a value in it, C11 would change to the ‘X’ with the red fill.
I hope this makes some sense

Thank you in advance

Craig

**shg** · 01-25-2010, 03:26 PM

Conditional formatting can only change the format of the cell, not the contents. To change the contents would require VBA.

**teelim** · 01-26-2010, 05:01 AM

Hi,
I think this is what you want. But this only works once. (I hope you understand my explanation below)

Here is what I mean. Cell B11 & C11 starts empty (there is a formula in cell C11). If you select anything in cell B11, a default result will appear in cell C11 based on the formula. But if you change value of C11 based on the drop-down list, then the formula will be lost - hence "it only works once".

another change I made in introducing an additional "value" (a space, " ") under "FOUND", so that C11 can show a blank when B11 is empty (based on the formula in C11)

*changes made:
1) in Sheet "JANUARY", formula added in cell C11
2) in Sheet "LISTS", a space, " " is inserted in cell D4

Hope this helps.

**craigproudfoot** · 01-26-2010, 02:52 PM

Thanks for all the help

That works Teelim, but as you say, only once.

Can anyone suggest the VBA to get this working constantly.

Cheers

craig

**teelim** · 01-27-2010, 12:08 AM

Hi craigproudfoot,
I am not good with macros but if you want something simple, I think this should work (but it also depends on your exact requirement). I'm not exactly sure what/how you want it but I made a simple "Reset" button on the side (you need to allow macros for your excel) which deletes cell B11 and reinserts the formula in C11.

This is as much as I can do with my very limited knowledge on macros. :P

Hope this helps.