Conditional formatting with multiple conditions

**wburbage** · 06-24-2014, 11:21 AM

I'm pretty new to conditional formatting so hopefully this is easier than I'm making it out. What formula can I use to highlight cells that meet the following two conditions... 1) DOES NOT contain an = sign and 2) contains a number <> 0.

I have a worksheet that's used to retrieve essbase data and that same worksheet also has columns for user input. Since Essbase doesn't work well with data entered straight into a cell it will wipe out any user entered data without an equals sign (don't ask). So I'm trying to set a conditional format that will highlight any cells where the user just entered the number without an equal sign. Then I can go into those cells and add the equal sign. If you have other ideas I'm more than open. If it were up to me I would have built a different model but I wasn't involved in the design.

Thanks in advance
Bill

**Richard Buttrey** · 06-24-2014, 12:20 PM

Hi,

If you have Excel 2013 then the answer is much simpler since there is a specific ISFORMULA() function that you can use.
Without it we'll have to resort to a more esoteric approach which has its roots in the XLM predecessor language to VBA macros.

First create a name 'IsFormula' and define it as

Formula:

=GET.CELL(48,INDIRECT("rc",FALSE))

Now use conditional format on the cells in question using the 'Use a formula....' method defined as

Formula:

=AND($A2<>0,NOT(IsFormula))

**wburbage** · 06-24-2014, 02:10 PM

Unfortunately I'm still on Excel 2010. I gave ISFORMULA a try but it didn't help. You got my hopes up there. There isn't a basic formula that will take care of it? I was hoping it was something simple that I was just overlooking.