Need a VBA code to Validate columns in spreadsheet

[levanoj]

I need a code that validates my spreadsheet by checking the following:

• All cell values under the 'Button Number' header (column A) must be a numeric value within range of 1-600
• All cell values under the 'Button Type' header (column B) can only be one of the following values: Speedial, ResourceAndSpeedDial, Resource, HuntAndSpeedDial, ICM, InvalidButtonType and must also be case sensitive
• All cell values under the 'Button Label' header (column C) can contain any alphanumeric value except when the cell value in column B of the same row is 'InvalidButtonType', in which case there should be no value present
• All cell values under the 'Button Lock' header (column D) can only be one of the following values: true, false and must also be case sensitive
• All cell values under the 'SpeedDialType' header (column E) can only be one of the following values: none, home, office, mobile and must also be case sensitive, except when the cell value in column B of the same row is 'InvalidButtonType', in which case there should be no value present
• All cell values under the 'Incoming Action Rings' header (column F) can only be one of the following values: none, repeat, single and must also be case sensitive, except when the cell value in column B of the same row is 'InvalidButtonType', in which case there should be no value present
• All cell values under the 'Incoming Action Priority' header (column G) can only be one of the following values: high, low and must also be case sensitive, except when the cell value in column B of the same row is 'InvalidButtonType', in which case there should be no value present
• All cell values under the 'Incoming Action Float' header (column H) can only be one of the following values: Float, NoFloat and must also be case sensitive, except when the cell value in column B of the same row is 'InvalidButtonType', in which case there should be no value present
• All cell values under the 'Display Incoming CLI' header (column I) can only be one of the following values: CLI, noCLI and must also be case sensitive, except when the cell value in column B of the same row is 'InvalidButtonType', in which case there should be no value present

Once the code is executed it should produce and X under the 'Error' header (column J) if any of the above listed rules are not met within that row. It would also be ideal if the code highlighted the cell where the rule was not met to be able to easily locate the cell and rectify issue. If no errors exist then the result would be a Pop-Up Notification of "Validation Successful"

I know much of this can be done via Conditional Formatting but because I receive many spreadsheets from different sources then being able to run a macro would be much more convenient.

​

The following is an example of how this would result:

Book2
	A	B	C	D	E	F	G	H	I	J
1	Button Number	Button Type	Button Label	Button Lock	SpeedDialType	Incoming Action Rings	Incoming Action Priority	Incoming Action Float	Display Incoming CLI	Error
2	1	MWI	Test1	false	none	repeat	high	Float	noCLI
3	2	ResourceAndSpeedDial	Test2	false	none	none	high	Float	noCLI
4	3	Resource	Test3	false	none	repeat	high	NoFloat	noCLI
5	4	HuntAndSpeedDial	Test4	false	home	single	high	NoFloat	noCLI
6	5	Resource	Test5	true	mobile	single	high	NoFloat	noCLI
7	6	InvalidButtonType	Test6	false						X
8	7	Resource	Test7	false	none	repeat	low	NoFloat	noCLI
9	8	ICM	Test8	false	none	repeat	low	NoFloat	CLI
10	9	Resource	Test9	false	office	repeat	low	NoFloat	CLI
11	10	Resource	Test10	FALSE	office	repeat	low	Float	CLI	X
12	11	Resource	Test11	false	none	repeat	low	Float	CLI
13	12	Resource	Test12	false	none	repeat	low	Float	CLI
14	13	Resource	Test13	false	none	repeat	low	float	CLI	X
15	14	Resource	Test14	false	none	repeat	low	NoFloat	CLI
16	15	Resource	Test15	false	none	repeat	low	NoFloat	CLI
17	16	Resource	Test16	false	none	repeat	low	NoFloat	CLI
18	17	Resource	Test17	false	none	repeat	low	NoFloat	CLI
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
D2:D10	List	true,false
D12:D18	List	true,false
I2:I18	List	noCLI,CLI
H2:H13	List	Float,NoFloat
H15:H18	List	Float,NoFloat
E2:E18	List	none,home,office,mobile
F2:F18	List	none,single,repeat
G2:G18	List	low,high
A2:A18	List	,null,null,null,null
B1:B18	List	InvalidButtonType,Resource,ResourceAndSpeedDial,HuntAndSpeedDial,Speedial,ICM

 

[diddi]

This is relatively easy to implement in VBA. if you have any experience with VBA then i can get you started and you can tinker and see how you go. one thought i have is that you are flagging errors in some cases which it is clear what the data is supposed to be (eg FALSE:false) instead of flagging them it would be much less work for the user to just have them corrected.

 

[levanoj]

I've dabbled a bit with VBA. I was hoping to get a preliminary code if possible and then I'd tweak it as necessary.

Regarding your other point, I'd actually prefer to see the error first rather than automatically having it corrected since I may have to verify directly with the source who provided the file before I can make that decision to correct the error.
It's really a work protocol I have to adhere to before editing these particular files. Hope that makes sense.

 

[diddi]

sure... well here is a basic starting point and i can add to it or you can have a go

Sub Validate()
    lastrow = Cells(Rows.Count, "A").End(xlUp).Row
    For Row = 2 To lastrow
        ' first column
        If Val(Cells(Row, 1)) < 1 Or Val(Cells(Row, 1)) > 600 Then
            Cells(Row, 1).BackColor = &H80FF&
            Cells(Row, 10) = "X"
        End If
        ' second column
        If Cells(Row, 2) <> "Speedial" And Cells(Row, 2) <> "ResourceAndSpeedDial" And Cells(Row, 2) <> "Resource" _
                    And Cells(Row, 2) <> "HuntAndSpeedDial" And Cells(Row, 2) <> "ICM" And Cells(Row, 2) <> "InvalidButtonType" Then
            Cells(Row, 2).BackColor = &H80FF&
            Cells(Row, 10) = "X"
        End If
        ' third column
        If Val(Cells(Row, 2)) = "InvalidButtonType" And Val(Cells(Row, 3)) <> "" Then
            Cells(Row, 3).BackColor = &H80FF&
            Cells(Row, 10) = "X"
        End If
        ' try some yourself
        
    Next Row
End Sub

 

[levanoj]

This was the result when running it on the example data in my original post:

Any suggestions?

 

[diddi]

Cells(row,2).Interior.ColorIndex = 46

sorry about that. thinking of a textbox background color

 

[levanoj]

So the highlight seems to work but got a different error this time

 

[diddi]

change <> "" to <> 0

 

[levanoj]

same error

 

[diddi]

        If Cells(Row, 2) = "InvalidButtonType" And Cells(Row, 3) <> "" Then

that will teach me for copy pasting and not checking. so do you see what the If blocks are doing?