Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Conditional formatting for cells in a column that contain formulas but have no values/are blank

0

I am working on a spreadsheet where I have entered a formula in column C.

Column C's value is a caculation based on columns A-B.

Sometimes column C is blank, but it still has a formula in it.

When cells in column C are blank (but contain a formula), I would like to set formatting a certain way (in this case no formatting).

The other cells are formatted based on their values as follows:

values greater than 5 and less than 11 are highlighed yellow

values greater than 11 are highlighted red

I have been trying to write formulas, trying to use the available options in conditional formatting, reordering the conditional formatting for this column, but nothing is working. I have also tried to search this specific issue without any sucess.  Can someone help me please.

Answer
Discuss

Answers

0
Selected Answer

A formula like IF(C3="") refers to the cell's value. It isn't suitable to determine if the cell contains a formula. For that purpose you might use the COUNTA function, here formulated for use in CF.

=COUNTA($C3)>0

will evaluate to TRUE if the cell contains anything, formula or an entry.

You might use the COUNTBLANK function to determine if a cell contains a formula which returns a blank.

=AND(COUNTA($C3)>0,COUNTBLANK($C3)=0)

Note that in CF the IF is implied. If you test the formula in a worksheet function it must be added.

Discuss
0

What are the conditional formats that you currently have? Can't you just add another conditional format that works when the cell is empty and sets the format to default white background? 

You could do that by using a formula as simple as this: =A1=""  where A1 is the cell to check.

Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login