check a range

[kevin carter]

Hi
i have a worksheet that the user is required to enter data
in row 27 columns c to z
what i want to to is check the cell data entered in and return a value
eg.
if the user enters a value in cells C27 to J27 i want to return A in
cell av1
if the user enters a value in cells k27 to R27 i want to return B in
cell av2
if the user enters a value in cells S27 to Z27 i want to return C in
cell av3

any ideas

thanks

kevin

[Bob Phillips]

'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE_1 As String = "C27:J27"
Const WS_RANGE_2 As String = "K27:R27"
Const WS_RANGE_3 As String = "S27:Z27"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE_1)) Is Nothing Then
Range("AV1").Value = "A"
ElseIf Not Intersect(Target, Me.Range(WS_RANGE_2)) Is Nothing Then
Range("AV2").Value = "B"
ElseIf Not Intersect(Target, Me.Range(WS_RANGE_3)) Is Nothing Then
Range("AV2").Value = "C"
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.





--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"kevin carter" <[email protected]> wrote in message
news:[email protected]...
> Hi
> i have a worksheet that the user is required to enter data
> in row 27 columns c to z
> what i want to to is check the cell data entered in and return a value
> eg.
> if the user enters a value in cells C27 to J27 i want to return A in
> cell av1
> if the user enters a value in cells k27 to R27 i want to return B in
> cell av2
> if the user enters a value in cells S27 to Z27 i want to return C in
> cell av3
>
> any ideas
>
> thanks
>
> kevin
>

[MDBJ]

"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> '-----------------------------------------------------------------
> Private Sub Worksheet_Change(ByVal Target As Range)
> '-----------------------------------------------------------------
> Const WS_RANGE_1 As String = "C27:J27"
> Const WS_RANGE_2 As String = "K27:R27"
> Const WS_RANGE_3 As String = "S27:Z27"
>
> On Error GoTo ws_exit:
> Application.EnableEvents = False
> If Not Intersect(Target, Me.Range(WS_RANGE_1)) Is Nothing Then
> Range("AV1").Value = "A"
> ElseIf Not Intersect(Target, Me.Range(WS_RANGE_2)) Is Nothing Then
> Range("AV2").Value = "B"
> ElseIf Not Intersect(Target, Me.Range(WS_RANGE_3)) Is Nothing Then
> Range("AV2").Value = "C"
> End If
>
> ws_exit:
> Application.EnableEvents = True
> End Sub
>
> 'This is worksheet event code, which means that it needs to be
> 'placed in the appropriate worksheet code module, not a standard
> 'code module. To do this, right-click on the sheet tab, select
> 'the View Code option from the menu, and paste the code in.


Damn... that's deep- try my 3 liner instead maybe...

in av1 =if(max(C27:J27)>0,"A","")
in av2 =if(max(k27:rJ27)>0,"B","")
in av2 =if(max(s27:zJ27)>0,"C","")