Is this the right approach:- vba to check value in each cell in a range is as expected

[chopperj]

hi all, <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I would be grateful if you could give me your view to whether this is the right approach:-<o></o>
<o></o>
<o></o>
There is a current process where many people send in data, in excel, on a monthly basis and this data is meant to be in the same structure, but it is not thus there is lots of tedious work involved of correcting the data. <o></o>
<o></o>
I am proposing to standardise the data collection process by sending out a data template with code in, to run once fully populated to review each cell to check that the data is as expected if not high light in red and add error counter to last column ie something like (this is free hand and not meant to read as pure code)-<o></o>
<o></o>

(note i thought of simply putting on validation on but some people write the data out but some copy and paste from other sources thus need solution that works regardless of the way the data is populated)<o></o>

<o></o>
'----------------------------------------------------------------<o></o>
sub findRangeLoopRows ()<o></o>
'headers are in row five<o></o>
<o></o>
'Identify full range<o></o>
bottomrow = range("a65336").end(xlup).row<o></o>
lastcolumnnum = range ("a5").end(xlright).column <o></o>
<o></o>
lastcolumnletter 'then run function to convert column number to column letter<o></o>
<o></o>
'loop through each row and run checkrow for each row<o></o>
for e = 6 to bottomrow<o></o>
currentrow = ("a" & e & ":" & lastcolumnletter & e)<o></o>
Checkrow currentrow<o></o>
<o></o>
next e<o></o>
<o></o>
end sub<o></o>
<o></o>
'------------------------------------------------------------<o></o>
<o></o>
sub checkrow (currentline as range)<o></o>
'each column has its own quirks so need to check each one......<o></o>
<o></o>
'column A is dates only<o></o>
if date(currentline.cells(1,1).value) is false then cells(1,1).colour = red<o></o>
<o></o>
'column B can only be one from a list of values<o></o>
if find(currentline.cells(1,2).value) in range ("columnBvalues") is false then cells(1,2).colour = red<o></o>
<o></o>
'etc<o></o>
'etc<o></o>
'etc<o></o>
<o></o>
'Lastly run code to count cells with colour of red colour and if > 1 show error message to advise user not correct by XX number and to review red cells<o></o>
<o></o>
'----------------------------------------------------------------------<o></o>
<o></o>
<o></o>

 

[SamTYler]

In a situation like that, I typically use a Form and never allow the User to see the Workbook. On the Form, I will use ListBoxes or ComboBoxes where possible to limit to entries to only those allowed and Programmatic Data Validation where I have to use a Textbox.