Validate

[kenpcli]

Why wont this script color the column M if it meets both criteria:

Sub Validate()


Dim found As Boolean
found = False
For Each cell In Range("F2:M1000").Cells
If cell.Value = "FT Hourly" And "<40" Then
found = True
End If
Next
If found = True Then
cell.Select.Interior.ColorIndex = 9

End If
End Sub

Thanks

 

[JoeMo]

This line:

If cell.Value = "FT Hourly" And "<40" Then

is likely the problem. What is the bit in red intended to do?

 

[reberryjr]

Can you upload a couple of rows that meet your condition? I'd like to see how the data is structured.

 

[Rick Rothstein]

Why wont this script color the column M if it meets both criteria:

Sub Validate()


Dim found As Boolean
found = False
For Each cell In Range("F2:M1000").Cells
If cell.Value = "FT Hourly" And "<40" Then
found = True
End If
Next
If found = True Then
cell.Select.Interior.ColorIndex = 9

End If
End Sub

What exactly is the red highlighted line of code above trying to test for? Either a cell has text in it or it has a number in it, both cannot be true (your use of the And operator makes it seem like you think it can).

Also, what is the overall purpose of this code? There are other structural problems with your code besides the above, but I won't know how to fix them until I know what you want this code to actually do.

 

[kenpcli]

I am trying to get it to look at column F (Pay Class)and find any FT Hourly it is FT Hourly then look at Column M (Total) in the same row and see if it is less 40, if so it color fills it.

[TABLE="width: 1225"]
<tbody>[TR]
[TD]Home Company[/TD]
[TD]Full Name[/TD]
[TD]ID Number[/TD]
[TD]Home Site[/TD]
[TD]Home Department[/TD]
[TD]Pay Class Name[/TD]
[TD]Regular [/TD]
[TD]Overtime [/TD]
[TD]PTO [/TD]
[TD]FTO [/TD]
[TD]REGPOR [/TD]
[TD]PORSL [/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]PCLI-Corp[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]30[/TD]
[TD]FT Hourly[/TD]
[TD="align: right"]40.00 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]40.00 [/TD]
[/TR]
[TR]
[TD]PCLI-Corp[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]30[/TD]
[TD]FT Hourly[/TD]
[TD="align: right"]39.95 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]39.95 [/TD]
[/TR]
[TR]
[TD]PCLI-Corp[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]30[/TD]
[TD]FT Hourly[/TD]
[TD="align: right"]40.00 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]40.00 [/TD]
[/TR]
[TR]
[TD]PCLI-Corp[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]30[/TD]
[TD]FT Hourly[/TD]
[TD="align: right"]39.75 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]39.75 [/TD]
[/TR]
[TR]
[TD]PCLI-Corp[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]30[/TD]
[TD]FT Hourly[/TD]
[TD="align: right"]26.80 [/TD]
[TD][/TD]
[TD="align: right"]13.20 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]40.00 [/TD]
[/TR]
[TR]
[TD]PCLI-Corp[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]30[/TD]
[TD]FT Hourly[/TD]
[TD="align: right"]40.00 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]40.00 [/TD]
[/TR]
[TR]
[TD]PCLI-Corp[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]30[/TD]
[TD]FT Hourly[/TD]
[TD="align: right"]31.25 [/TD]
[TD][/TD]
[TD="align: right"]8.75 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]40.00 [/TD]
[/TR]
[TR]
[TD]PCLI-Corp[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]30[/TD]
[TD]FT Hourly[/TD]
[TD="align: right"]40.00 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]40.00 [/TD]
[/TR]
[TR]
[TD]PCLI-Corp[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]30[/TD]
[TD]Hourly PT[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0.00 [/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup>[/TABLE]

 

[JoeMo]

Maybe:

Sub Validate()
Dim cell As Range
Application.ScreenUpdating = False
For Each cell In Range("F2:F1000")
If cell.Value = "FT Hourly" And cell.Offset(0, 7).Value < 40 Then cell.Offset(0, _
    7).Interior.ColorIndex = 9
Next cell
Application.ScreenUpdating = True
End Sub

 

[Rick Rothstein]

Here is another macro for you to consider...

[table="width: 500"]
[tr]
	[td]Sub Validate()
  Dim R As Long
  Application.ScreenUpdating = False
  For R = 2 To Cells(Rows.Count, "F").End(xlUp).Row
    If Cells(R, "F").Value = "FT Hourly" And Cells(R, "M").Value < 40 Then Cells(R, "M").Interior.ColorIndex = 9
  Next
  Application.ScreenUpdating = True
End Sub[/td]
[/tr]
[/table]

 

[kenpcli]

Thank you, worked like a charm.