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.

Sample where a specific cell is referenced
LVL 11
RayData AnalystAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rob HensonFinance AnalystCommented:
The cell to which the Conditional Formatting refers is dynamic.

If you highlight the range of cells required to have the formatting and then build the formula so that it refers to the top left of that range, the formatting will be applied to all cells selected.

So for your example above, if you have selected AC3 to AL50 and you build the formula exactly as it is above (no $ symbols to lock any row or column) it will adjust for the cells selected and the equivalent in AL50 will be ISFORMULA(AL50).

Also, with conditional formatting you don't need the IF statement; that part is built in, "IF condition is met use Format specified", so you only actually need:


Rob H

EDIT Sorry, you want Formatting applied when cells is not a formula:


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.

Saurabh Singh TeotiaCommented:
The formula that you are applying =ISFORMULA is only available in excel 2013 version and it's not available in the other versions which is 2007/2010

So if you want a formula to work in all the versions..i defined a formula for me which does that what i'm looking for..

so i went into formula-->name manager-->=GET.CELL(48,INDIRECT("rc",FALSE))

Post that i gave this name as Checkformula

Now i selected the cells where i wanted to applied conditional formatting and said Not(Checkformula) and applied my conditional formatting and it does what i'm looking for..

Enclosed is a sample file for your reference which i created in 2010 and does what you are looking for...

Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

RayData AnalystAuthor Commented:
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 :-)
Saurabh Singh TeotiaCommented:
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...
RayData AnalystAuthor Commented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.