• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

vba code for countif function and background change

P

paradise

Member
Hi all of u,

With the help of this forum I am able to accomplish many tasks.Currently I am looking two things in vba code.

1.In column Q I have used the formula, the same I want to use thru VBA.
2.If any of the data is present in column K: P then Column Q data should be visible else data will be invisible,say suppose the fonts will get converted into white background if neither of the cell contains data in each row from column K to P.This I require in VBA.

I have recd the vba code in one of the forum(http://www.excelforum.com/showthread.php?t=1085777&p=4090024) but it is displaying the formula it should however be shown as value form and other values in hidden form.

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
t = Target.Address

If InStr(t, ":") > 0 Then
'MultiCell
MyArray = Split(t, ":")
ColA = Range(MyArray(0)).Column
ColB = Range(MyArray(1)).Column
RowA = Range(MyArray(0)).Row
RowB = Range(MyArray(1)).Row

If ColA < 11 Or ColB > 16 Or RowA < 4 Then Exit Sub

For RowCount = RowA To RowB

For Count = 11 To 16
temp = temp & Cells(RowCount, Count).Value
Next

If temp = "" Then

Cells(RowCount, 17).Clear
Else
Cells(RowCount, 17).FormulaR1C1 = "=RC[-16]&""-""&COUNTIF(R4C1:RC[-16],RC[-16])"
End If

Next

Else

If Target.Rows.Count > 1 Or Target.Column < 11 Or Target.Column > 16 Or Target.Row < 4 Then Exit Sub
For Count = 11 To 16
temp = temp & Cells(Target.Row, Count).Value
Next
If temp = "" Then

Cells(Target.Row, 17).Clear
Else
Cells(Target.Row, 17).FormulaR1C1 = "=RC[-16]&""-""&COUNTIF(R4C1:RC[-16],RC[-16])"
End If

End If
End Sub

Hope anyone can resolve it .
 
Last edited by a moderator:
I think it did not work.There is in Column Q it is showing formula also.Refer Q7,Q8,Q12,Q14.
I have added your code too.Pls find enclosed in attachment.
 

Attachments

  • Conditional formatting v 1.xlsm
    986 KB · Views: 3
Last edited:
1st Requirement : as mentioned above
As I did not like to show formula in Q column.It should be hidden.Manually when data is typed in column K,L,M,N,O and P,then column Q should display the result in value form.

This is so becoz in actual the labels in column K3,L3,M3,N3,O3,P3 represent the worksheets name which I have not shown here and typed data manually.
2nd Requirement:
I have added only one worksheet PG and used a formula in A2, currently it is displaying the result zero this is so becoz the data in column Q there is no data.So I want this to happen that it should be hidden and linked in that particular column Q but in other cell/worksheets it should be linked up & function.

I am expecting the Column Q data to be hidden becoz the data from column K to P comes from the worksheets label of K3,L3,M3,N3,O3,P3.And the data of column Q is linked.So whenever the data is linked of column Q,it should function.

Hope in this part you can help.

So, when I use the formula in existing or another worksheet/s relating with column Q data,Column Q data should function irrespective of hidden.

1st Column Q should be unhidden when there is data from K to P.
2nd In other worksheets Column Q should work irrespective of data hidden/unhidden.

Hope this is suffice info.
If further info required kindly let me know.
 
Try this,
Code: 
Dim A As Range, r As Range
    Set A = Range("Q4:Q100")
    For Each r In A
        If r.HasFormula And r.Value > 0 Then
            r.Value = r.Value
        End If
    Next r
 
I think the first requirement is fulfilled.It worked.I have a confusion with
Set A = Range("Q4:Q100")
does it limit to Q100 rows only.In real my data will be there for thousands of rows and not as attached workbook which is only a sample.

Thanks for the code.

Can you kindly see my second requirement.The case of data linking from Column Q to another worksheet named'PG' with no change in displaying the data in Q column.
 
You must log in or register to reply here.
Back
Top