select Range Code
 

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

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