A Working IF Statement that I cannot adapt for Conditional Formatting

[netrixuser]

Hello all,

My goal is to highlight a row of cells where one cell in that row contains more than 4 commas.

I started with this IF just to prove the logic and it returns TRUE

=IF(E37="",0,LEN(E37)-LEN(SUBSTITUTE(E37,",","")))>4

My Conditional Format Formula currently looks like this - but it does nothing. Even though cell E37 contains 12 commas

="IF(E37="""",0,LEN(E37)-LEN(SUBSTITUTE(E37,"","","""")))>4"

Am I doing something obviously wrong - or is there an easier way to achieve my goal of highlighting a row if one cell in that row contains more than 4 commas ?

Any help very much appreciated.

Regards

Netrix

 

[SanjayGMusafir]

Important is that your problem has been resolved. Good to know that.

Still for your understanding -

• What I could make from your original post that you are using CF in a wrong way.
• What @Fluff and me suggested are more or less same formula that we derived from your work itself.
• What needs to be understood is that you can't use IF statement in conditional format - so I used AND function to match it up.
• Moreover the way your original formula in CF appears, it's likely that you didn't start it formula with = sign

Hope it helps you sometime in future.

Thanks for your feedback

 

[Fluff]

What needs to be understood is that you can't use IF statement in conditional format

Yes you can, but it is rarely needed. The 1stformula the OP posted should quite happily work in CF

 

[netrixuser]

I'm posting this here because it is regarding the Substitute command that was given earlier to count Commas in a cell - but I am now trying to use this in VBA and have issues.
Note: not wishing to use Conditional Formatting this time, I have that working thanks to this site, I now need to count the number of times a user has an entry where there are more than 4 commas.
To explain further, and apologies for not using XL2BB - I'm on a work computer
Mock up sheet below: For each user in column C (User) I need to look in Column D (Call ID) and count the number of times that there are 4 or more commas. Finally place that number as a running total in Column H (Commas)

	Recording	User	Call ID	Active Users	MP4	Nothing	Commas	CdT
	aswed.mp4	Bill	,,,,,,	Chris
	.mp4	Bill	,,	Barry
	.opus	Charlie	,,	Bill	2​
	.mp4	Charlie	,,,,	Nicolas
	.opus	Charlie	,,,,	Charlie	1​
	.mp4	Harry	,	Frank
	.opus	Sophie	,	Robin
	.opus	Sophie	,,,,	Gill		1​
	.	Gill	,,	Hasan
	.	Pete	,,,	Harry	1​
	.mp4	Barbara	,,,,	Sophie
	.	Barbara	,,,,	Paul
	.	Barbara	,,,,	Pete		1​
	.	Barbara	,	George
	.mp4	Barbara	,	Barbara	2​	3​
	.mp4	Cyril	,,	Cyril	1​	2​

I have this code that Counts the number of times a user has a filename that ends in ".mp4" (Filename is in Column B - "Recording")

Sub Test_Count_Number_of_Alerts()
Dim U As Variant
Dim ws As Worksheet
    Set ws = ActiveWorkbook.Sheets("Sheet1")
Dim UsID As Variant 'User

Dim lastRow As Long
    lastRow = Cells(Rows.Count, 4).End(xlUp).Row
Dim UsIDRnge As Range
    Set UsIDRnge = ws.Range("C2:C" & lastRow) 'range containing End User IDs
         
Dim LookupRange As Range
Set LookupRange = ws.Range("E1:E50") 'Range containing All users
    

Dim UsidPlace As Variant 'Variable - to store location of count
'Dim i As Long

Dim CntMP4 As Long, CntBlank As Long, CntCDT As Long, CntComma As Long
CntMP4 = 0
CntBlank = 0
CntCDT = 0
CntComma = 0

        For Each U In UsIDRnge
            
               If Right(U.Offset(0, -1), 4) = ".mp4" Then
                    UsidPlace = "F" & Application.Match(U, LookupRange, 0)
                        CntMP4 = CntMP4 + 1
                             Range(UsidPlace).Value = Range(UsidPlace).Value + CntMP4
                        CntMP4 = 0
                End If
            Next U

As you can see in the spreadsheet - the users who have ".mp4" in Column B have numbers against the names in Columns E (Active Users) and F (MP4)
This is a running total, so if I use the code on another csv file, Bill, for example, might have 4 more instances of .mp4 so he would have a running total of 6

When I try to modify the IF statement above to use the Substitute entry I was given earlier in this thread I get and error

        For Each U In UsIDRnge
        
                If Len(U.Offset(0, 1)) - Len(WorksheetFunction.Substitute(U.Offset(0, 1), "", "", """")) > 4 Then
                    UsidPlace = "H" & Application.Match(U, LookupRange, 0)
                        CntComma = CntComma + 1
                            Range(UsidPlace).Value = Range(UsidPlace).Value + CntComma
                        CntComma = 0
                End If

        Next U

I tried to use hard coded cell address instead of Variable and Offset but get the same error

If Len("D2") - Len(Application.WorksheetFunction.Substitute("D2", "", "", """")) > 4 Then

The error is Runtime Error 1004

"Unable to get the Substitute property of the WorkSheetFunction class" at the line of code above

Wondering if it cannot be done this way in VBA and I need another approach to count the number of times the cells containing Commas have more than 4 of them ?

All help gratefully received

 

[Fluff]

As this is now a significantly different question, it needs a new thread. Thanks