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 Highlight Cells Criteria

0

Thank you for the answer below, it was exactly what I was looking for!

Hello, 

     I am attempting to Highlight all blank cells in a table, but this is a little different. I have a table of about 1,000 Rows. This table will be populated manually over time. I would like to hightlight cells with missing information however I need to omit rows that have zero data

In the example attached, row 24 and beyond have zero data and should not be highlighted. I used -

Conditional Formatting -

     Format only cells that contain -

            Format only cells with (Blanks)

Thanks in advance for any help. 

Mike.

excel
MS Office Version: 1912
posted by: mherrman (rep: 2) on Tue Jan 14, 2020 at 3:10 pm
Answer
Discuss

Discussion

Did you modify your question after it was answered? This forum can't function in that way. If you have a new question start a new thread. They cost nothing.
Variatus (rep: 4889) Jan 15, '20 at 9:07 pm
Add to Discussion

Answers

0
Selected Answer

Select cell A4 and modify your existing second CF rule to work with this formula.

=AND(COUNTA($A4:$I4),ISBLANK(A4))

Leave the Applies To range the way it is now (A4:i502) or change it after applying the new formula.

For your better understanding, COUNTA($A4:$I4) counts the number of entries - text or number - in the given range. It returns zero if there are none. In context COUNTA($A4:$I4) is evaluated like IF(COUNTA($A4:$I4),True,False). In this context the IF function returns True if COUNTA($A4:$I4) is any number other than zero. The syntax below is therefore equivalent.

=AND(COUNTA($A4:$I4)>0,ISBLANK(A4))
posted by: Variatus (rep: 4889) on Tue Jan 14, 2020 at 8:45 pm
Discuss

Discussion

Thank you for the answer, this was excactly what I was looking for!
mherrman (rep: 2) Jan 15, '20 at 1:38 pm
Add to Discussion


Answer the Question

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