Link to home
Create AccountLog in
Avatar of Ray
RayFlag for United States of America

asked on

Excel Conditional Formatting - format group of cells based on formula with not static formula

I'd like to conditionally format a range of cells to highlight a color if the cell does NOT have a formula (let's me know if a user has keyed over it).  I can do this for a cell using a formula, but the formula references a specific cell so I'd have to do this for all cells in question.  I need of version of this that applies itself to whatever cell is being evaluated.  I'm open to a different formula as well.  This was simply one way to get there.

User generated image
ASKER CERTIFIED SOLUTION
Avatar of Rob Henson
Rob Henson
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
If you don't want users overwriting the cell contents, you may consider protecting the worksheet. If there are cells the user needs to make changes to make sure to un-check the cell protection before protecting the worksheet.

Krompton
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Ray

ASKER

Wow, plenty of good information.

Rob - Exactly what I was looking to figure out

Krompton - I don't care if they do change them as it is sometime necessary, I just need to know IF they did.

Saurabh - Being the long time expert you are, I appreciate the way you covered all the bases with your solution.  
QUESTION on this, if I use the ISFORMULA in a conditional format and someone open in Excel 2010 or earlier, will it cause a problem or simply not apply the formatting?  If no error, then I don't really care if others see the formatting change as long as I do :-)
If the person open this in excel 2010 the conditional formatting won't be applied once the calculation is triggered since this formula in not their in 2010 it will give a #Name error as a result your formatting won't be applied...

Just tested the same as well since i have both versions of excel in this pc..The conditional formatting gets applied in 2013 only...
Avatar of Ray

ASKER

Rob's solution was the EXACT thing I was looking for and covered my concerns on this specific issue.
Split points because Saurabh's solution was insightful and could've been THE solution under other circumstances.