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

 

[levanoj]

So all 3 validation rules in the code seem to be working as requested in my original post except for the 3rd one:

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

While it does correctly highlight the cell in column C if there are any characters present when column B is "InvalidButtonType", I left out that it should also highlight the cells in columns E - I if there are any characters present. Only column D is allowed to have characters without producing an error. Can that part be adjusted?

 

[diddi]

they can be made to perform any logic step you like. have you tried doing the one for col D

 

[diddi]

        ' third column
        For Col = 3 To 8
            If Cells(Row, 2) = "InvalidButtonType" And Cells(Row, Col) <> "" And Col <> 4 Then
                Cells(Row, Col).Interior.ColorIndex = 46
                Cells(Row, 10) = "X"
            End If
        Next Col

 

[levanoj]

That did the trick, thanks for the help. I'm going to keep building on this code and if I encounter any roadblocks I may re-post but appreciate all the help

 

[diddi]

thx for feedback. thats fine if you need to follow up something

 

[levanoj]

So I tried to add some adjustments to the code and nothing happens when I ty to run it. Any advice as to what may be wrong with it?

Sub BtnValidate()
    lastrow = Cells(Rows.Count, "A").End(xlUp).Row
    For Row = 2 To lastrow
        ' Action column
        If Val(Cells(Row, 1)) <> "Update" Or Val(Cells(Row, 1)) <> "Ignore" Then
            Cells(Row, 1).Interior.ColorIndex = 46
            Cells(Row, 35) = "X"
        End If
        ' Button Number column
        If Val(Cells(Row, 6)) < 1 Or Val(Cells(Row, 6)) > 600 Then
            Cells(Row, 6).Interior.ColorIndex = 46
            Cells(Row, 35) = "X"
        End If
        ' Button Type column
        If Cells(Row, 7) <> "Speedial" And Cells(Row, 7) <> "ResourceAndSpeedDial" And Cells(Row, 7) <> "Resource" _
                    And Cells(Row, 7) <> "HuntAndSpeedDial" And Cells(Row, 7) <> "ICM" And Cells(Row, 7) <> "InvalidButtonType" _
                    And Cells(Row, 7) <> "MWI" And Cells(Row, 7) <> "OneButtonDivert" And Cells(Row, 7) <> "OneButtonICMDivert" _
                    And Cells(Row, 7) <> "KeySequence" And Cells(Row, 7) <> "PointOfContact" And Cells(Row, 7) <> "PersonalPointOfContact" _
                    And Cells(Row, 7) <> "SimplexConference" And Cells(Row, 7) <> "DuplexConference" Then
            Cells(Row, 7).Interior.ColorIndex = 46
            Cells(Row, 35) = "X"
        End If
        ' Button Type column
        For Col = 3 To 8
            If Cells(Row, 7) = "InvalidButtonType" And Cells(Row, Col) <> "" And Col <> 10 Then
                Cells(Row, Col).Interior.ColorIndex = 46
                Cells(Row, 35) = "X"
            End If
        
    Next Col
    Next Row
End Sub

 

[diddi]

ok so there are a couple of things to note from my original code...
if the cell you are looking at has a number in it, then i used
Val(Cells(row,1)) <> 0 ----> this checks that the value of the cell (if it is a number, but even if it looks like a number) is not 0
if a cell should have a string in it, then i dont use Val( ) because the numeric value of "Update" does not make much sense.
in your new code above i can see
' Action column
If Val(Cells(Row, 1)) <> "Update" Or Val(Cells(Row, 1)) <> "Ignore" Then

do you see the problem here? you are getting the value of a word. it should be
If Cells(Row, 1) <> "Update" Or Cells(Row, 1) <> "Ignore" Then

but then there is the logic to consider. this can do your head in a bit LOL
the AND operator means one condition and also the other condition must both be true for the operation to return true
the OR operator means one or the other or both are true for the operation to return true.
so in this case above:
you if it is not "update" AND also it is not "ignore" then it is wrong... do you see the logic?

Final correction for this line:

        If Cells(Row, 1) <> "Update" And Cells(Row, 1) <> "Ignore" Then

there may be others to check, come back after you have a play (or your head is done in ROFL)

 

[levanoj]

Appreciate the clarification. So the adjusted line code as shown:

If Cells(Row, 1) <> "Update" And Cells(Row, 1) <> "Ignore" Then

is sort of working but not as expected. What's happening is that the cell is highlighting only when there are actual characters (letters or #'s) within the cell other than "update" and "Ignore". I was also expecting that the cell be highlighted if it is blank. Any guidance on how to make that happen?