Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
how can i select the data from 6th row 7th column using VBA
I have 11 columns in my excel sheet.Few fields are mandatory and few are
optional.once the cursor is moved to the next row an error message should be pointed out,if the mandatory field in the earlier row is missing and those cells should be highlighted.Can anyone please help me.. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
how can i select the data from 6th row 7th column using VBA
Try the below..Right click the sheet tabView Code and paste the below code..
Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Target, Range("A:K")) Is Nothing Then If Trim(Target) < "" Then Target.Interior.ColorIndex = xlColorIndexNone End If End If End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim lngcol As Long Dim strCol As String, arrCol As Variant Dim blnBlank As Boolean strCol = "1,2,3,4,5" If Target.Row 1 Then If Not Application.Intersect(Target, Range("A:D")) Is Nothing Then If Target.Interior.ColorIndex < 6 Then Application.EnableEvents = False arrCol = Split(strCol, ",") For lngcol = 0 To UBound(arrCol) If Trim(Cells(Target.Row - 1, 0 + arrCol(lngcol))) = "" Then Cells(Target.Row - 1, 0 + arrCol(lngcol)).Interior.ColorIndex = 6 blnBlank = True End If Next If blnBlank Then MsgBox "Mandatory fields blank" Application.EnableEvents = True End If End If End If End Sub -- If this post helps click Yes --------------- Jacob Skaria "Bond" wrote: I have 11 columns in my excel sheet.Few fields are mandatory and few are optional.once the cursor is moved to the next row an error message should be pointed out,if the mandatory field in the earlier row is missing and those cells should be highlighted.Can anyone please help me.. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
how can i select the data from 6th row 7th column using VBA
missed out to mention few points
--Specify the columsn which are mandatory in the string strcol. Currently 1-5 colums are set as mandatory strCol = "1,2,3,4,5" --Change the below line to suit If Not Application.Intersect(Target, Range("A:D")) Is Nothing Then to suit your requirement. If it is 1st 11 columns the range should be "A:K" If this post helps click Yes --------------- Jacob Skaria "Bond" wrote: I have 11 columns in my excel sheet.Few fields are mandatory and few are optional.once the cursor is moved to the next row an error message should be pointed out,if the mandatory field in the earlier row is missing and those cells should be highlighted.Can anyone please help me.. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA: Column Select then Data Select then return to cell A1 | Excel Discussion (Misc queries) | |||
select max value from row of data and column containing max value | Excel Worksheet Functions | |||
Select last data in column for a graph | Charts and Charting in Excel | |||
Shortcut to select column with data in previous column | Excel Discussion (Misc queries) | |||
Select Filtered Data less One column | Excel Programming |