• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 460
  • Last Modified:

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
0
Ray
Asked:
Ray
2 Solutions
 
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:

=ISFORMULA(AC3)

Thanks
Rob H

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

=NOT(ISFORMULA(AC3))
0
 
KromptonCommented:
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
0
 
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...

Saurabh...
checkformula.xlsm
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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 :-)
0
 
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...
0
 
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now