Count total blank Cells & Highlight Cell it

Greetings,

Hope you having a great time.

I want to a "Macro click button" to Count total number of blank Cells & Highlight Cell 'L4' only when full range 'B4:K4' is blank along with Person name. Is it possible

Please find the Excel Attachment file

Hight light & Count Blank.xlsx

any help will be highly appreciated.

Best,

Try this:

Please Login or Register  to view this content.

Good catch Sintek. Thanks

Hi,

Highly appreciated for quick Solution. code works good but their some misunderstanding i think,

When their is a value in 'B:K' range don't highlight but only count total blank cells. and When their NO value in 'B:K' range then highlight and count as well. Also highlight name of Column A.

Please find the attachment.
count-total-blank-cells-and-highlight-cell01.xlsm

Best,

You have highlighted the second name but the is a value in one of the rows, in your notes you state that this shouldn't be highlighted. Which way round is it please?

Originally Posted by PFDave

You have highlighted the second name but the is a value in one of the rows, in your notes you state that this shouldn't be highlighted. Which way round is it please?

Yes, If their is a value in one of the rows in Column range ('B:K') this shouldn't be highlighted. Only be highlighted if their is No values.

Best,

Originally Posted by sintek

hi pipsmultan

Let me see if i understand this
If the empty cell count between B & K = 10 Then l must = 10 and be highlighted and the name in A must be highlighted
If there is values in cells then put the value of empty cells in L and don't highlight.

Yes, you understand very well what i am trying to achieve BUT (If there is values in cells then put the value of empty cells in L and don't highlight) and don't highlight name in A as well.

Best,

Please Login or Register  to view this content.

or if you want the name to appear in L then:

Please Login or Register  to view this content.

Sorry Sintek, stepping on your toes, I'm out now

Originally Posted by sintek

If so then this code will do the trick...What about your initial request of putting the name in Col L

Work Great but when i refresh the screen by pressing the Command button it doesn't refresh the screen/values, i have to clear the highlighted cell manually. Is their any way that when i every press the command button it refresh.

Please find the excel file
count-total-blank-cells-and-highlight-cell02.xlsm

Best,

seeing as Sintek is otherwise engaged try:

Please Login or Register  to view this content.

Originally Posted by PFDave

seeing as Sintek is otherwise engaged try

Hi,

Sorry for late response, Code work exactly as i required, Thanks you & 'sintek' as well you make my problem solve

1. I Just want to know if i want to count less numbers of Column/Cells, i.e If i want to count only Column (B:F & H & J:K) dont want to count (G & I) how can i do that.

2. If i insert new Column and want to count that new insert Column how can i do that also.

Best,

Originally Posted by sintek

In answer to Question2...New column insert
Change all the L's to M's and all the K's to L's in your code

Will come back to you on Question 1

Superb many thanks

Originally Posted by sintek

Will come back to you on Question 1

Hi,

any luck so far regarding the solution for Question 1.

Thanks

Best,

Originally Posted by sintek

Took me a while but this is the best i could do....

Hi,

Thanks for the solution. i tried it but it not giving the required results.
i have uploaded the excel file, i might be doing something wrong. please guide

count-total-blank-cells-and-highlight-cell04.xlsm

Best,

Hi,

I Just want to to count =B:F,H,J:K and their Total sum of blank Cells count (in between =B:F,H,J:K) in Column 'L',

Best,

pipsmultan

Used Conditional formatting.

Please Login or Register  to view this content.

Originally Posted by sintek

This one got me stumped hey...I learned something new today.....Finally a solution..

Bingo, Superbly solved Worked exactly i am trying from long time.

just one quick question As i mention in Post '#19' if in future in need to insert New Column how i can add it in the your formula like i.e if i insert two column 'L & M' and i need to to count =B:F, H, J:M and their Total sum of blank Cells count in column 'N' do i have add it like this

rng1 = WorksheetFunction.CountBlank(.Range(Cells(i, 2), Cells(i, 6)))
rng2 = WorksheetFunction.CountBlank(.Cells(i, 8))
rng3 = WorksheetFunction.CountBlank(.Range(Cells(i, 10), Cells(i, 11), Cells(i, 12), Cells(i, 13)))

Please guide, so that my future problem is also solved. As my current Big problem is all sloved

Best,

Originally Posted by jindon

pipsmultan

Used Conditional formatting.

@jindon,

Thank you sooooooo much for your kind solution and response highly appreciated.

Your solution works great also i hope i will use it in future.

Best

Originally Posted by pipsmultan

2. If i insert new Column and want to count that new insert Column how can i do that also.

Just run once and insert col.L:M.
This is how combination of conditional formatting and formula acts.

Please Login or Register  to view this content.

perfect, I haven't tried it, But i am sure it will Work like charm

Thank you 'sintek', PFDave & jindon' without your kind help i cant figure it out.

Best to all,

Thread is marked as SOLVED & Reputation Added.