Validate

K

kenpcli

Board Regular
Joined
Oct 24, 2017
Messages
129
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
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Sort by date Sort by votes
This line:

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

is likely the problem. What is the bit in red intended to do?
 
Upvote 0
Can you upload a couple of rows that meet your condition? I'd like to see how the data is structured.
 
Upvote 0
kenpcli said:
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
Click to expand...
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.
 
Last edited:
Upvote 0
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]
 
Upvote 0
Maybe:
Code: 
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
 
Upvote 0
Here is another macro for you to consider...
Code: 
[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]
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question Question
Excel VBA Searching Unique Variables to Match against Exported Schedule Files to Determine Date
Replies
1
Views
158
sijpie
sijpie
A
  • Solved
Workbook_BeforeClose() is not preventing user to close even when specific ranges are blank
Replies
4
Views
291
John_w
J
S
  • Question Question
The number 0 is not displayed in the cell
Replies
3
Views
265
myall_blues
myall_blues
P
  • Question Question
Duplicates and Combining multiple cells into 1 cell
Replies
6
Views
478
Patriot2879
P
M
  • Question Question
Not running code
Replies
0
Views
207
MagicCarpet
M

Forum statistics

Threads
1,223,911
Messages
6,175,324
Members
452,635
Latest member
laura12345

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top