select Range Code
Soniya,
Not sure what you mean, with the colors, but you can use code like that below to find if the date
has already been entered into your data table.
HTH,
Bernie
MS Excel MVP
Sub TryNow()
Dim myCell As Range
Dim myRow As Long
On Error GoTo NoMatch:
myRow = Application.Match(CLng(DateValue(TxtDate)), Range("A:A"), False)
Set myCell = Range("A" & myRow)
myCell.Offset(0, TxtArvCol) = TxtCraType
Range(myCell.Offset(0, TxtArvCol.Value), _
myCell.Offset(0, TxtDepCol - 1)).Interior.ColorIndex = TxtColor
Exit Sub
NoMatch:
Range("A65536").End(xlUp).Offset(1, 0) = TxtDate
Range("A65536").End(xlUp).Offset(0, TxtArvCol) = TxtCraType
Range(Range("A65536").End(xlUp).Offset(0, TxtArvCol.Value), _
Range("A65536").End(xlUp).Offset(0, TxtDepCol - 1)).Interior.ColorIndex = TxtColor
End Sub
"Soniya" wrote in message
oups.com...
Hi All,
I have the following in my sheet
column A Date
Column B code
Column C thre EP3 time 00:00 thru 23:50 in 10 Minutes interval
My user from has date, time from, timeto, type
I am updating the data from a userform
Range("A65536").End(xlUp).Offset(1, 0) = TxtDate
Range("A65536").End(xlUp).Offset(0, TxtArvCol) = TxtCraType
Range(Range("A65536").End(xlUp).Offset(0, TxtArvCol.Value),
Range("A65536").End(xlUp).Offset(0, TxtDepCol - 1)).Interior.ColorIndex
= TxtColor
In my above code i want make a modification to get the following:
1) check the date (columnA) and if the the date is the same for the
next entry instead of adding a new record i want have the interior
color in the same row.
2) if there is already an interior color code in any of the cell in the
range Range(Range("A65536").End(xlUp).Offset(0, TxtArvCol.Value),
Range("A65536").End(xlUp).Offset(0, TxtDepCol - 1)) then I have to add
a new record and not in the same row.
I hope I am clear in my explanation.
Thanks
Soniya
|