test of CF

[coltaalex]

test for the existence of CF condition in a loop:
for
=LEN(TRIM(P11))=0 the code is :




Dim N As Long, i as long
i = 0
For N = 1 To 9989
If rtrim(Cells(N, 16).text = "" Then
i = i + 1

End If
Next N
if i > 0 then
msgbox "Your message here"
end if


how to do it with :

"=COUNTIF(Code,P11)"

[mdmackillop]

I don't know what is being asked here.

[coltaalex]

Why this code is not working ?, i mean the "for loop"

it doesn't like the "count if"






Application.Goto Reference:="R11C16:R10000C16"
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=COUNTIF(Code,P11)=0"
Selection.FormatConditions(Selection.FormatConditions.count).SetFirstPriori ty
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False



Dim N As Long, i As Long
i = 0
For N = 11 To 21
If countif(Cells(N, 16).Text) = "" Then
i = i + 1

Exit For
Else

End If
Next N

If i > 0 Then
MsgBox ("Hello")
End If

Range("P11").Select


End Sub

[mdmackillop]

Can you post a sample file on which to test your code?

[coltaalex]

2L-T8-HARD 2L-T8HYB 2LT-BB 2LTD 2LR-ELB 2LRPC 2LRT 2LR-T8 2LRT-ELB

[coltaalex]

2LR-ELB xx 2LRPC gggg 2LRT 2LR-T8 dd h 2LRT-ELB

[coltaalex]

how to post a sample ???

[coltaalex]

it's a column(P11) with different codes(numbers and letter),
the first part of the code ( the countif ) is testing if i have the same code in other column(Code-named by me), and if is not such code in Code column, then is making the cell red,

the second part of the code is for displaying a message, if such code don't exist in the Code - column,

[mdmackillop]

Use Manage Attachments in the Go Advanced reply section

[coltaalex]

here is it

[mdmackillop]

What is this indended to do?

For N = 1 To 21
If Application.CountIf(Cells(N, 4).Text) = "" Then
i = i + 1
Exit For
Else
End If
Next N

[coltaalex]

message box if i have red cells

[mdmackillop]

Please be less cryptic. Countif is used to count a range of cells that meet a criteria. You are looking at one cell at a time. Please explain your logic here.

[coltaalex]

it steel not working is showing : debug with error code 424

[mdmackillop]

If you don't answer questions I cannot assist.

[coltaalex]

sorry i didn't see your question
my logic is to display a message box, every time when i at least one have red cell, colored by the condition Count if,

[coltaalex]

this make sens ?

[mdmackillop]

Option Explicit
Sub Code()
Dim N As Long, i As Long

With Range("D1:D6")
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=COUNTIF(Code,D1)=0"
.FormatConditions(.FormatConditions.Count).SetFirstPriority
With .FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
End With
.FormatConditions(1).StopIfTrue = False
End With

i = 0
For N = 1 To 21
If Cells(N, 4) <> "" Then
i = i + (Application.CountIf(Range("Code"), Cells(N, 4)) = 0)
End If
Next N
If i <> 0 Then
MsgBox "Hello! " & -i & " red cells found"
End If
End Sub