I am back needing help again. I have written an error check sub routine to check for blank required cells before the row status is completed. I am getting a compile error, “Method or Data Member not Found” and I don’t understand the Microsoft help file. Could someone help me and explain what I am missing please? The Sign_In sheet is where the data is entered and column “K” is the final status for the row. It will display the fill of the cell green and the ending code will be shown in the cell. For the ending code “ATR” in column “K”, cells in columns “I”, “J”, “L” can’t be blank or empty in that row. The cells missing the data will fill red and the “K” column will turn yellow to indicate a problem in that row. The code worked fine until I changed it to a sub routine. All help is greatly appreciated!
I have uploaded a sample workbook.
Here is my code:
Sub ATR()
Dim Sign_In As Excel.Worksheet
Set Sign_In = ThisWorkbook.Sheets("Sign_In")
If Sheets.Range("I" & Srn) = "" Then 'Error check for column I.
Sheets.Range("I" & Srn).Interior.Color = RGB(255, 0, 0) 'Changes Fill in column I to red to Inticate empty cell.
Sheets.Range("K" & Srn).ClearContents 'Clears contents of Column K if column I is empty.
Sheets.Range("K" & Srn).Interior.Color = RGB(255, 0, 0) 'Sets background color column K to Red if there is an empty cell that is required.
MsgBox "Enter DR#, Press OK to enter data in that cell " 'Messge to user on error.
Sheets.Range("K" & Srn).Interior.Color = RGB(255, 255, 0) 'Sets background color column K to Yellow to indicate incomplete data entry.
Else 'Error check for column J.
Sheets.Range("I" & Srn).Interior.Color = Range("A" & Srn).Interior.Color 'Sets shading color to the row shade color.
Sheets.Range("K" & Srn).Interior.Color = RGB(0, 255, 0) 'Sets background color column K to Green.
If Sheets.Range("J" & Srn) = "" Then 'Check for empty required cells in row.
Sheets.Range("J" & Srn).Interior.Color = RGB(255, 0, 0) 'Changes Fill in column I to red to Inticate empty cell.
Sheets.Range("K" & Srn).ClearContents 'Clears contents of Column K if column I is empty.
Sheets.Range("K" & Srn).Interior.Color = RGB(255, 0, 0) 'Sets background color column K to Red if there is an empty cell that is required.
MsgBox "Enter Vehicle Removal Report Number, Press OK to enter data in that cell. "
Sheets.Range("K" & Srn).Interior.Color = RGB(255, 255, 0) 'Sets background color column K to Yellow to indicate incomplete data entry.
Else
Sheets.Range("J" & Srn).Interior.Color = Range("A" & Srn).Interior.Color 'Sets shading color to the row shade color.
Sheets.Range("K" & Srn).Interior.Color = RGB(0, 255, 0) 'Sets background color column K to Green.
If Sheets.Range("L" & Srn) = "" Then 'Check for empty required cells in row.
Sheets.Range("L" & Srn).Interior.Color = RGB(255, 0, 0) 'Changes Fill in column I to red to Inticate empty cell.
Sheets.Range("K" & Srn).ClearContents 'Clears contents of Column K if column I is empty.
Sheets.Range("K" & Srn).Interior.Color = RGB(255, 0, 0) 'Sets background color column K to Red if there is an empty cell that is required.
MsgBox "Enter Hearing Officer, Press OK to enter data in that cell. "
Sheets.Range("K" & Srn).Interior.Color = RGB(255, 255, 0) 'Sets background color column K to Yellow to indicate incomplete data entry.
Else
Sheets.Range("L" & Srn).Interior.Color = Range("A" & Srn).Interior.Color 'Sets shading color to the row shade color.
Sheets.Range("K" & Srn).Interior.Color = RGB(0, 255, 0) 'Sets background color column K to Green.
End If
End If
End If
End Sub
Thanks again for any help received.
Bookmarks