VBA highlight and count empty cells in a range of data

[canarycat123]

Hi there - beginner to VBA here, so what I'm about to ask is way out of knowledge base, but am wondering if someone can help.

I would like code that is able to do the following:

1. Within a range of data highlight empty cells in yellow up until the last populated row. That will need to be dynamic as rows will continue to be added, and any empty cells there should also be highlighted.
2. Count the number empty cells within that range and display the number on another sheet.
3. When an empty cell is filled the highlight should be removed and the count reduced on the other sheet.

 

[Dan_W]

Does that need to be in VBA? That looks like it can be accomplished with a simple conditional formatting rule (for items 1 and 3) and the COUNTBLANK formula (for item 2).

 

[canarycat123]

Does that need to be in VBA? That looks like it can be accomplished with a simple conditional formatting rule (for items 1 and 3) and the COUNTBLANK formula (for item 2).

Thanks - I was overcomplicating the highlight element and have now implemented that solution. In terms of the COUNTBLANK formula, is there a way of making that dynamic to capture cells in the row beneath when new data is added?

For example, in column A I have reference numbers. In columns C-G I have data associated with each unique reference. Currently that data covers C9:G67. When a new reference is added to A68 can the COUNTBLANK formula then adapt to capture any blank cells in C68:G68? Then so on and so forth as new references are added to column A?

Sorry if the answer is obvious, but I'm very new to combining Excel formula's, VBA etc.

 

[Dan_W]

As in the example below, you can prepopulate a cell with a formula that calculates on certain conditions. So for example, the formula in column I will count how many blanks are in the cell range Column C to G in it's row. I've put that formula in every cell of Column I until Row 10, and so you will see that from Row 4 onwards, it starts saying 5 blank cells (even though there is no entry). In Column J, I've adjusted the formula so that if there is no unique reference number in column A, then it doesn't calculate anything. The moment you put any content into Column A, it will automatically calculate the number of blank cells.

Book1
	A	B	C	D	E	F	G	H	I	J
1	Reference	Date	FirstName	LastName	Age	Location	Occupation		BlankCells	SmartBlankCells
2	A001	15/09/2021	Hattori	Hanzo		Japan	Sword Maker		1	1
3	A002	16/09/2021		Potter	15	UK			2	2
4	A003	17/09/2021	Alice			Wonderland	Tresspasser		2	2
5									5
6									5
7									5
8									5
9									5
10									5
Sheet1

Cell Formulas
Range	Formula
I2:I10	I2	=COUNTBLANK(C2:G2)
J2:J10	J2	=IF(A2<>"",COUNTBLANK(C2:G2),"")

Is that what you're after?

 

[canarycat123]

As in the example below, you can prepopulate a cell with a formula that calculates on certain conditions. So for example, the formula in column I will count how many blanks are in the cell range Column C to G in it's row. I've put that formula in every cell of Column I until Row 10, and so you will see that from Row 4 onwards, it starts saying 5 blank cells (even though there is no entry). In Column J, I've adjusted the formula so that if there is no unique reference number in column A, then it doesn't calculate anything. The moment you put any content into Column A, it will automatically calculate the number of blank cells.

Book1
	A	B	C	D	E	F	G	H	I	J
1	Reference	Date	FirstName	LastName	Age	Location	Occupation		BlankCells	SmartBlankCells
2	A001	15/09/2021	Hattori	Hanzo		Japan	Sword Maker		1	1
3	A002	16/09/2021		Potter	15	UK			2	2
4	A003	17/09/2021	Alice			Wonderland	Tresspasser		2	2
5									5
6									5
7									5
8									5
9									5
10									5
Sheet1

Cell Formulas
Range	Formula
I2:I10	I2	=COUNTBLANK(C2:G2)
J2:J10	J2	=IF(A2<>"",COUNTBLANK(C2:G2),"")

Is that what you're after?

Thank you so much - that's exactly what I need! Works perfectly

 

[Dan_W]

Glad to hear it. I'd be grateful if you could please mark it as the solution so that others will know that it was helpful.