-
test of CF
test for the existence of CF condition in a loop:
for
=LEN(TRIM(P11))=0 the code is :
[VBA]
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
[/VBA]
how to do it with :
"=COUNTIF(Code,P11)"
Last edited by coltaalex; 07-07-2010 at 11:48 AM.
-
I don't know what is being asked here.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
-
Why this code is not working ?, i mean the "for loop"
it doesn't like the "count if"
[VBA]
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
[/VBA]
-
Can you post a sample file on which to test your code?
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
-
2L-T8-HARD 2L-T8HYB 2LT-BB 2LTD 2LR-ELB 2LRPC 2LRT 2LR-T8 2LRT-ELB
-
2LR-ELB xx 2LRPC gggg 2LRT 2LR-T8 dd h 2LRT-ELB
-
-
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,
-
Use Manage Attachments in the Go Advanced reply section
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
-
-
What is this indended to do?
[vba]
For N = 1 To 21
If Application.CountIf(Cells(N, 4).Text) = "" Then
i = i + 1
Exit For
Else
End If
Next N
[/vba]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
-
message box if i have red cells
-
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.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
-
it steel not working is showing : debug with error code 424
-
If you don't answer questions I cannot assist.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
-
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,
-
-
[VBA]
Option Explicit
Sub Code()
Dim N As Long, i As Long
With Range("D16")
.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
[/VBA]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules