Color Alternate Rows and columns

Color Alternate Columns

Color Alternate Columns - Last Cell Method

Color alternate columns - using INPUT BOX

MOD Function - Color alternate rows

Private Sub CommandButton1_Click()
Dim rng As Range
Set rng = Range("B4:F15")
rng.Select
Dim i As Integer
For i = 1 To Selection.Rows.Count
If i Mod 2 = 0 Then
Selection.Rows(i).Interior.ColorIndex = 7
End If
Next
End Sub

Double Ruled

Through this program we can can create required range into double ruled

Private Sub CommandButton1_Click()
Dim wkb As Workbook
Set wkb = Workbooks.Add
Dim i As Long
Dim US As Range
Set US = wkb.Sheets("Sheet1").Range("A1:O15")
For r = 1 To US.Rows.Count
If r Mod 2 <> 0 Then
US.Columns(r).ColumnWidth = 21
US.Rows(r).RowHeight = 25
Else
US.Columns(r).ColumnWidth = 1
US.Rows(r).RowHeight = 2
US.Rows(r).Interior.ColorIndex = 39
US.Columns(r).Interior.ColorIndex = 39
End If
Next
End Sub

Data Validation

We have to use Validation Property

Data Validation - Create Drop-down

Private Sub CommandButton1_Click()
With Range("A1:A11").Validation
'deletes existing validation
.Delete
'add data validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="=sheet2!E1:E11"
'it ignores the cell value is blank in range("A1:A11")
.IgnoreBlank = True
.InCellDropdown = True
'it displays error message
.ErrorMessage = "Select from dropdown"
End With
End Sub

Data Validation - with named ranges

Private Sub CommandButton1_Click()
Dim r As Integer
r = Range("A5").End(xlDown).Row
'Define DATAVALIDATION in H1 Cell
With Range("H1").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="=sheet2!A5:A" & r
.ErrorMessage = "Please select from dropdown"
.IgnoreBlank = True
.InCellDropdown = True
End With
End Sub