VBA code to count the no of cells from one sheet to another with its background color.

Hi,
I am new to VBA, please help me in this!
 How to write VBA code for  counting cells from one sheet to  copy the count value to  another sheet   with its background color.
this is code i tried,


Sub Workbook_Open()
Dim str As String
str = range("d4").Value
With Sheets("newCorp")
With .range("d6")
'.Formula = "=COUNTIFS(" & str & "!F:O," & str & ")"
End With
End With
range("d6").Select
range("F10:010").Copy newCorp.range("d6" & Rows.Count).End(xlUp)(2)
'Selection.AutoFill Destination:=Range("d6")
'Range("d6").Select
End Sub
Saha HAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ShumsDistinguished Expert - 2017Commented:
Hi Saha,

Welcome to Expert Exchange!

Please post your sample/dummy workbook.
0
Saha HAuthor Commented:
Hi,
Please find the attached sheet.
here it has to count no of passed cells from sheet2 and paste the count in newcorp with its background color.

Please help!
Thanks in advance.
0
ShumsDistinguished Expert - 2017Commented:
No attachment :(
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Saha HAuthor Commented:
0
ShumsDistinguished Expert - 2017Commented:
Do you mean in attached...

Click Get Count and check the result.
Saha_C_Countif.xlsm
0
Saha HAuthor Commented:
Thank you!!!  Its fine.
with that we can copy the detail of cells?
I mean why it failed like that.
0
ShumsDistinguished Expert - 2017Commented:
What do you mean by details? You wanted just count.
0
Saha HAuthor Commented:
Ya that's correct sir.
some failed cases are also there know how it will define ?
how many failed and details for that . Is it possible to do?
0
ShumsDistinguished Expert - 2017Commented:
Where do you want the count of Fail? In attached I got it in Col E.
Saha_C_Countif_v3.xlsm
0
Saha HAuthor Commented:
In WW31 column i.e.,  col D  only i need that details. In fail some information also written write we have to copy that also.
If pass no change but if it fails we need details for failure .
0
ShumsDistinguished Expert - 2017Commented:
Check in last version, I have list in Col E from E12
0
Saha HAuthor Commented:
ok I will check...
0
Saha HAuthor Commented:
Once again thank you.....
0
ShumsDistinguished Expert - 2017Commented:
Does it means, provided solution serves your purpose?
0
Saha HAuthor Commented:
yes thank you sir.
0
ShumsDistinguished Expert - 2017Commented:
Then I would request you to please close this question by following below link, if you are not aware:
How do I close my question?
0
Saha HAuthor Commented:
Before that i want to ask you one question  that,  can we insert one more row in between to copy the fail detail ? Is it possibe?
Sorry for disturbing again and again just i want to ask this.
0
Saha HAuthor Commented:
previous one is also good but i think this is more effective.
0
ShumsDistinguished Expert - 2017Commented:
There is already 1 row gab between Pass Count & Fail Details (which is row 11), do you want one more row in between?
0
Saha HAuthor Commented:
no no not like that , i'm telling where we mention the pass count know below that only it is possible?
0
ShumsDistinguished Expert - 2017Commented:
It is not possible in your workbook as it has structured.

Increase the row height of 11th row and display as Details of Fail, check in attached...
Saha_C_Countif_v3.xlsm
0
Saha HAuthor Commented:
in same cell we can't mention both fail and pass?
0
Saha HAuthor Commented:
Hi,
i can do like this ,Please check attachment (corp sheet).
Saha_C_Countif_v3.xlsm
0
ShumsDistinguished Expert - 2017Commented:
No. Its not possible
0
Saha HAuthor Commented:
OK thank you.
0
Saha HAuthor Commented:
.FormulaR1C1 = "=IF(COUNTIF(Sheet2!R[5]C[2]:R[5]C[11],""PASS"")=0,"""",COUNTIF(Sheet2!R[5]C[2]:R[5]C[11],""PASS""))"

Sir,  Please can u explain how you selected this formula?
0
Saha HAuthor Commented:
If I add one more col in between ,  red color for failure is coming..
Please check in attached sheet...
Countifnew.xlsm
0
ShumsDistinguished Expert - 2017Commented:
Formula I used is simple CountIf, If in Sheet 2 range F10:Q10 contains "PASS", then count "PASS", it goes down as the number of rows.
=IF(COUNTIF(Sheet2!F10:O10,"PASS")=0,"",COUNTIF(Sheet2!F10:O10,"PASS"))
same with "<>PASS" not equal to Pass.
Kindly note, when you define variables in VBA, you cannot change/insert/delete cells/rows/columns. If you do so, then you need to change in VBA as well, inserting one more column in newCorp sheet between D & E, you need to change this range as well .Range("E5:E" & LRow)

Hope this helps.
0
Saha HAuthor Commented:
Sorry for disturbing again and again, but we want data in E col only know then which col we have to mention there.
i am changing the col but its not applying..
0
ShumsDistinguished Expert - 2017Commented:
Ok.

Check in attached..
Countifnew_2.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Saha HAuthor Commented:
OK Thank you.
This is for one row if we added one more sheet then we have to do same for that?
0
ShumsDistinguished Expert - 2017Commented:
Of Course. For every sheet and every workbook, You need to change variables.
0
Saha HAuthor Commented:
Only Variables we need to change right?  If we copy paste the same code means it will work right?
0
Saha HAuthor Commented:
Because like Sheet2 so many sheets are there , that's why i am asking
0
ShumsDistinguished Expert - 2017Commented:
Sorry that was not initial requirement. Code provided for one sheet.
0
Saha HAuthor Commented:
Can we paste that failure details in that same cell?
0
ShumsDistinguished Expert - 2017Commented:
You already asked this earlier and I replied not possible.
0
Saha HAuthor Commented:
Yes, that was task given by manager for one sheet but now he has given some more sheets to add .
0
Saha HAuthor Commented:
no i am asking where we paste the failure count know with that only we can't do?
0
Saha HAuthor Commented:
instead of pasting count of failure , can we store details there?
i mean in leaving that count can we store directly details there?
0
Saha HAuthor Commented:
Thank you sir.. Now i will close the question.
0
Saha HAuthor Commented:
Sir help me please...
If i have added new sheet it is not working for that.
0
Saha HAuthor Commented:
Still my  question is not completed....
0
Saha HAuthor Commented:
Hi Shums,
 Can you help me in this please....
0
Saha HAuthor Commented:
I want like this ... it has to copy the count and details of failure like in sheet "corp"
It has to take count the pass and fail from different sheets like ww31 , ww33 and more  and also copy the failure details in "newcorp"
new2.xlsm
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.