change row color according to an if statement

[Sten Melin]

Hi

I got a user that will paste information into an excel sheet.(sheet 1)

i have alreday recorded a macro (probably need to record a new one)
that copies the format from sheet 2 and paste it onto sheet one.
Sheet 2 is already row conditional formated to row 1000.

This works but its a bit slow.

So i was wondering to use something like this (see below).

But as im a newbie to programing so i dont know how to change the "Select
Case
LCase(Target.Value)" to an if statement and that the change macro should only
be applied up to and incl. the last row of information.

thank you,

Sten

---------------------------------------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'David McRitchie, 2004-09-26, programming, Case -- Entire Row
' http://www.mvps.org/dmcritchie/excel/event.htm#case
If Target.Column <> 6 Then Exit Sub 'Column F is column 6
If Target.Row = 1 Then Exit Sub
Application.EnableEvents = False 'should be part of Change macro
Select Case LCase(Target.Value)
Case "yes"
Target.EntireRow.Interior.ColorIndex = 34
Case "no"
Target.EntireRow.Interior.ColorIndex = 36
Case Else
Target.EntireRow.Interior.ColorIndex = xlColorIndexAutomatic
End Select
Application.EnableEvents = True 'should be part of Change macro
End Su
----------------------------------------------------------------------------------------------