Sub checkall()
Dim iCntr As Integer
iCntr = 0
Set ws = Sheets("mini sized DOW")
For Each rw In ws.Range("A3:A" & Range("B3").End(xlDown).Row)
For Each cel In Range("$AY$" & rw.Row & ":$AZ$" & rw.Row & ",$BB$" & rw.Row & ",$BD$" & rw.Row)
For Each cf In cel.FormatConditions
frmla = cf.Formula1
frmla = Application.ConvertFormula(frmla, xlA1, xlR1C1, , cf.AppliesTo.Cells(1, 1))
frmla = Application.ConvertFormula(frmla, xlR1C1, xlA1, , cel)
If Evaluate(frmla) Then
If cf.Interior.Color <> 5296274 Then stts = "": Exit For
iCntr = iCntr + 1
If iCntr > 3 Then
stts = "green"
End If
End If
Next cf
If stts <> "green" Then Exit For
Next cel
If stts = "green" Then cplist = cplist & rw.Cells(1, 2) & ", "
stts = ""
Next rw
MsgBox "All cells are green for" & vbCrLf & Left(cplist, Len(cplist) - 2)
End Sub
Sub checkall()
Dim iCntr As Integer
iCntr = 0
Set ws = Sheets("mini sized DOW")
For Each rw In ws.Range("A3:A" & Range("B3").End(xlDown).Row)
For Each cel In Range("$AY$" & rw.Row & ":$AZ$" & rw.Row & ",$BB$" & rw.Row & ",$BD$" & rw.Row)
For Each cf In cel.FormatConditions
frmla = cf.Formula1
frmla = Application.ConvertFormula(frmla, xlA1, xlR1C1, , cf.AppliesTo.Cells(1, 1))
frmla = Application.ConvertFormula(frmla, xlR1C1, xlA1, , cel)
If Evaluate(frmla) Then
If cf.Interior.Color <> 5296274 Then stts = "": Exit For
iCntr = iCntr + 1
If iCntr > 3 Then
stts = "green"
Exit For
End If
End If
Next cf
If stts <> "green" Then Exit For
Next cel
If stts = "green" Then cplist = cplist & rw.Cells(1, 2) & ", "
stts = ""
Next rw
MsgBox "All cells are green for" & vbCrLf & Left(cplist, Len(cplist) - 2)
End Sub
ASKER
ASKER
ASKER
Sub checkall()
Dim iCntr As Integer
Dim ws As Worksheet
Dim rw As Range
Dim cel As Range
Dim cf As FormatCondition
Dim frmla As String
Dim stts As String
Dim cplist As String
cplist = ""
Set ws = Sheets("mini sized DOW")
For Each rw In ws.Range("A3:A" & Range("B3").End(xlDown).Row)
iCntr = 0
For Each cel In Range("$AY$" & rw.Row & ":$AZ$" & rw.Row & ",$BB$" & rw.Row & ",$BD$" & rw.Row)
For Each cf In cel.FormatConditions
frmla = cf.Formula1
frmla = Application.ConvertFormula(frmla, xlA1, xlR1C1, , cf.AppliesTo.Cells(1, 1))
frmla = Application.ConvertFormula(frmla, xlR1C1, xlA1, , cel)
If Evaluate(frmla) Then
If cf.Interior.Color <> 5296274 Then
stts = ""
Else
stts = "green"
End If
Exit For
End If
Next cf
If stts = "green" Then
iCntr = iCntr + 1
If iCntr > 3 Then
Exit For
End If
Else
Exit For
End If
Next cel
If stts = "green" Then
cplist = cplist & rw.Cells(1, 2) & ", "
End If
stts = ""
Next rw
MsgBox "All cells are green for" & vbCrLf & Left(cplist, Len(cplist) - 2)
End Sub
ASKER
ASKER
ASKER
ASKER
ASKER
ASKER
ASKER
ASKER
ASKER
ASKER
ASKER
Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.
TRUSTED BY
ASKER
Just to explain why I need an alert or pop-up when four rows are highlighted green. Each green highlight signifies that a lot of buyers are buying a particular stock in the 'Dow Jones 30', however I would need to know that at least four stocks are being bought before I enter into a trade.
Cheers