Hi,

I have some a column N in a sheet called Raw Data. I have a cell dropdown
saying "Yes" or "No".

It could be possible that this Yes or No data is pasted from some other
workbook in to Column N.

I desire that if any row (starting from row no. 3 to used rows) within
Column N has Yes then all columns from O to AG for that row change to Brown
fill color or
something like that. ( Actually if column N is a NO then its an indication
that rest of the columns for that row will be blank)

I thought of a crude code like the following but not able to convert it in
to proper VB language.

Private Sub Worksheet_Change(ByVal Target As Range)
dim noofrows as integer
noofrows = ?? ' Dunno a crisp formula for calculating the noofrows
For i = 3 to noofrows
If cells(noofrows,14) = "Yes" then
Range("N"&noofrows&":AG"&noofrows).interior.colori ndex = 6 ' Actually I know
that yellow is 6 But I prefer red or brown color to shade the culprit cells.
End if
Next noofrows
End Sub

Regards,
Hari
India

Try this Hari

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Target.Column = 14 And Target.Row 2 Then
If LCase(Target.Value) = "yes" Then
Range(Cells(Target.Row, "O"), Cells(Target.Row, "AG")) _
.Interior.ColorIndex = 3 ' red
Else
Range(Cells(Target.Row, "O"), Cells(Target.Row, "AG")) _
.Interior.ColorIndex = xlColorIndexNone
End If
End If
End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Hari" wrote in message ...
Hi,

I have some a column N in a sheet called Raw Data. I have a cell dropdown
saying "Yes" or "No".

It could be possible that this Yes or No data is pasted from some other
workbook in to Column N.

I desire that if any row (starting from row no. 3 to used rows) within
Column N has Yes then all columns from O to AG for that row change to Brown
fill color or
something like that. ( Actually if column N is a NO then its an indication
that rest of the columns for that row will be blank)

I thought of a crude code like the following but not able to convert it in
to proper VB language.

Private Sub Worksheet_Change(ByVal Target As Range)
dim noofrows as integer
noofrows = ?? ' Dunno a crisp formula for calculating the noofrows
For i = 3 to noofrows
If cells(noofrows,14) = "Yes" then
Range("N"&noofrows&":AG"&noofrows).interior.colori ndex = 6 ' Actually I know
that yellow is 6 But I prefer red or brown color to shade the culprit cells.
End if
Next noofrows
End Sub

Regards,
Hari
India

Hi Ron,

Thanx a lot for ur code.

I can work with it.

Only one change I desired if possible. Usually this sheet might be populated
by pasting data from another sheet

So many columns and rows of data could be pasted in this enmasse.

When u say "If Target.Cells.Count 1 Then Exit Sub"
I believe that would mean that if in column N, If Yes or NO is pasted in 4
rows then it wouldnt work. ( For testing this I deleted that statement and
tried the code and it didnt change the selection fill color automatically)

Also the worksheet from which data is copied and pasted here would be such
that we will be pasting data from column K through N or from column L
through N. For example if I copy K3:N7 and I paste it in K8 then the code
doesnt work

So in above cases though the column N gets changed the code doesnt work.

In short, Is it possible for the "Target.Cells.Count " to work when the
count is greater than 1.

Please guide me.

( I have slightly modified to code to suit my requirements..Please find it
pasted below my signature)

Regards,
Hari
India


Private Sub Worksheet_Change(ByVal Target As Range)
'If Target.Cells.Count 1 Then Exit Sub ' I have made this line as a
comment by entering an apostrophe thinking that
If Target.Column = 14 And Target.Row 2 Then

If LCase(Target.Value) = "yes" Then
Range(Cells(Target.Row, "O"), Cells(Target.Row, "AG")) _
.Interior.ColorIndex = 3 ' red

ElseIf LCase(Target.Value) = "no" Then

Range(Cells(Target.Row, "O"), Cells(Target.Row,
"AG")) _
.Interior.ColorIndex = xlColorIndexNone

ElseIf Target.Value < "" Then
MsgBox "Please enter only yes or no in Column N"
Selection.ClearContents
Exit Sub

End If

End If


End Sub



"Ron de Bruin" wrote in message
...
Try this Hari

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Target.Column = 14 And Target.Row 2 Then
If LCase(Target.Value) = "yes" Then
Range(Cells(Target.Row, "O"), Cells(Target.Row, "AG")) _
.Interior.ColorIndex = 3 ' red
Else
Range(Cells(Target.Row, "O"), Cells(Target.Row, "AG")) _
.Interior.ColorIndex = xlColorIndexNone
End If
End If
End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Hari" wrote in message
...
Hi,

I have some a column N in a sheet called Raw Data. I have a cell
dropdown
saying "Yes" or "No".

It could be possible that this Yes or No data is pasted from some other
workbook in to Column N.

I desire that if any row (starting from row no. 3 to used rows) within
Column N has Yes then all columns from O to AG for that row change to
Brown
fill color or
something like that. ( Actually if column N is a NO then its an
indication
that rest of the columns for that row will be blank)

I thought of a crude code like the following but not able to convert it
in
to proper VB language.

Private Sub Worksheet_Change(ByVal Target As Range)
dim noofrows as integer
noofrows = ?? ' Dunno a crisp formula for calculating the noofrows
For i = 3 to noofrows
If cells(noofrows,14) = "Yes" then
Range("N"&noofrows&":AG"&noofrows).interior.colori ndex = 6 ' Actually I
know
that yellow is 6 But I prefer red or brown color to shade the culprit
cells.
End if
Next noofrows
End Sub

Regards,
Hari
India

Hi Hari

You can use Conditonal formatting on the Format menu

Select O3:AG?
FormatConditonal formatting
Formula =
=($N3="yes")
click on the format button to pick a color

You can make one for "no" also



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Hari" wrote in message ...
Hi Ron,

Thanx a lot for ur code.

I can work with it.

Only one change I desired if possible. Usually this sheet might be populated
by pasting data from another sheet

So many columns and rows of data could be pasted in this enmasse.

When u say "If Target.Cells.Count 1 Then Exit Sub"
I believe that would mean that if in column N, If Yes or NO is pasted in 4
rows then it wouldnt work. ( For testing this I deleted that statement and
tried the code and it didnt change the selection fill color automatically)

Also the worksheet from which data is copied and pasted here would be such
that we will be pasting data from column K through N or from column L
through N. For example if I copy K3:N7 and I paste it in K8 then the code
doesnt work

So in above cases though the column N gets changed the code doesnt work.

In short, Is it possible for the "Target.Cells.Count " to work when the
count is greater than 1.

Please guide me.

( I have slightly modified to code to suit my requirements..Please find it
pasted below my signature)

Regards,
Hari
India


Private Sub Worksheet_Change(ByVal Target As Range)
'If Target.Cells.Count 1 Then Exit Sub ' I have made this line as a
comment by entering an apostrophe thinking that
If Target.Column = 14 And Target.Row 2 Then

If LCase(Target.Value) = "yes" Then
Range(Cells(Target.Row, "O"), Cells(Target.Row, "AG")) _
.Interior.ColorIndex = 3 ' red

ElseIf LCase(Target.Value) = "no" Then

Range(Cells(Target.Row, "O"), Cells(Target.Row,
"AG")) _
.Interior.ColorIndex = xlColorIndexNone

ElseIf Target.Value < "" Then
MsgBox "Please enter only yes or no in Column N"
Selection.ClearContents
Exit Sub

End If

End If


End Sub



"Ron de Bruin" wrote in message
...
Try this Hari

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Target.Column = 14 And Target.Row 2 Then
If LCase(Target.Value) = "yes" Then
Range(Cells(Target.Row, "O"), Cells(Target.Row, "AG")) _
.Interior.ColorIndex = 3 ' red
Else
Range(Cells(Target.Row, "O"), Cells(Target.Row, "AG")) _
.Interior.ColorIndex = xlColorIndexNone
End If
End If
End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Hari" wrote in message
...
Hi,

I have some a column N in a sheet called Raw Data. I have a cell
dropdown
saying "Yes" or "No".

It could be possible that this Yes or No data is pasted from some other
workbook in to Column N.

I desire that if any row (starting from row no. 3 to used rows) within
Column N has Yes then all columns from O to AG for that row change to
Brown
fill color or
something like that. ( Actually if column N is a NO then its an
indication
that rest of the columns for that row will be blank)

I thought of a crude code like the following but not able to convert it
in
to proper VB language.

Private Sub Worksheet_Change(ByVal Target As Range)
dim noofrows as integer
noofrows = ?? ' Dunno a crisp formula for calculating the noofrows
For i = 3 to noofrows
If cells(noofrows,14) = "Yes" then
Range("N"&noofrows&":AG"&noofrows).interior.colori ndex = 6 ' Actually I
know
that yellow is 6 But I prefer red or brown color to shade the culprit
cells.
End if
Next noofrows
End Sub

Regards,
Hari
India

Hi Ron,

Wanted to avoid conditional formatting as on pasting data etc it gets
overwritten ( also otherwise it gets inadvertently changed by the end
user..)

Hence wanted to set it up sort of permanently.

Sorry to bother u but just wanted to know if "Target" in the argument of
Worksheet_Change can take only one cell as range. I mean if lots of cells
are pasted together it doesnt work...

Regards,
Hari
India
"Ron de Bruin" wrote in message
...
Hi Hari

You can use Conditonal formatting on the Format menu

Select O3:AG?
FormatConditonal formatting
Formula =
=($N3="yes")
click on the format button to pick a color

You can make one for "no" also



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Hari" wrote in message
...
Hi Ron,

Thanx a lot for ur code.

I can work with it.

Only one change I desired if possible. Usually this sheet might be
populated
by pasting data from another sheet

So many columns and rows of data could be pasted in this enmasse.

When u say "If Target.Cells.Count 1 Then Exit Sub"
I believe that would mean that if in column N, If Yes or NO is pasted
in 4
rows then it wouldnt work. ( For testing this I deleted that statement
and
tried the code and it didnt change the selection fill color
automatically)

Also the worksheet from which data is copied and pasted here would be
such
that we will be pasting data from column K through N or from column L
through N. For example if I copy K3:N7 and I paste it in K8 then the
code
doesnt work

So in above cases though the column N gets changed the code doesnt work.

In short, Is it possible for the "Target.Cells.Count " to work when the
count is greater than 1.

Please guide me.

( I have slightly modified to code to suit my requirements..Please find
it
pasted below my signature)

Regards,
Hari
India


Private Sub Worksheet_Change(ByVal Target As Range)
'If Target.Cells.Count 1 Then Exit Sub ' I have made this line
as a
comment by entering an apostrophe thinking that
If Target.Column = 14 And Target.Row 2 Then

If LCase(Target.Value) = "yes" Then
Range(Cells(Target.Row, "O"), Cells(Target.Row,
"AG")) _
.Interior.ColorIndex = 3 ' red

ElseIf LCase(Target.Value) = "no" Then

Range(Cells(Target.Row, "O"), Cells(Target.Row,
"AG")) _
.Interior.ColorIndex = xlColorIndexNone

ElseIf Target.Value < "" Then
MsgBox "Please enter only yes or no in Column
N"
Selection.ClearContents
Exit Sub

End If

End If


End Sub



"Ron de Bruin" wrote in message
...
Try this Hari

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Target.Column = 14 And Target.Row 2 Then
If LCase(Target.Value) = "yes" Then
Range(Cells(Target.Row, "O"), Cells(Target.Row, "AG")) _
.Interior.ColorIndex = 3 ' red
Else
Range(Cells(Target.Row, "O"), Cells(Target.Row, "AG")) _
.Interior.ColorIndex = xlColorIndexNone
End If
End If
End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Hari" wrote in message
...
Hi,

I have some a column N in a sheet called Raw Data. I have a cell
dropdown
saying "Yes" or "No".

It could be possible that this Yes or No data is pasted from some
other
workbook in to Column N.

I desire that if any row (starting from row no. 3 to used rows)
within
Column N has Yes then all columns from O to AG for that row change
to
Brown
fill color or
something like that. ( Actually if column N is a NO then its an
indication
that rest of the columns for that row will be blank)

I thought of a crude code like the following but not able to convert
it
in
to proper VB language.

Private Sub Worksheet_Change(ByVal Target As Range)
dim noofrows as integer
noofrows = ?? ' Dunno a crisp formula for calculating the noofrows
For i = 3 to noofrows
If cells(noofrows,14) = "Yes" then
Range("N"&noofrows&":AG"&noofrows).interior.colori ndex = 6 '
Actually I
know
that yellow is 6 But I prefer red or brown color to shade the
culprit
cells.
End if
Next noofrows
End Sub

Regards,
Hari
India

Hi Hari

Try this one

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
For Each cell In Target.Cells
If cell.Column = 14 And cell.Row 2 Then
If LCase(cell.Value) = "yes" Then
Range(Cells(cell.Row, "O"), Cells(cell.Row, "AG")) _
.Interior.ColorIndex = 3 ' red
Else
Range(Cells(cell.Row, "O"), Cells(cell.Row, "AG")) _
.Interior.ColorIndex = xlColorIndexNone
End If
End If
Next cell
End Sub

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Hari" wrote in message ...
Hi Ron,

Wanted to avoid conditional formatting as on pasting data etc it gets
overwritten ( also otherwise it gets inadvertently changed by the end
user..)

Hence wanted to set it up sort of permanently.

Sorry to bother u but just wanted to know if "Target" in the argument of
Worksheet_Change can take only one cell as range. I mean if lots of cells
are pasted together it doesnt work...

Regards,
Hari
India
"Ron de Bruin" wrote in message
...
Hi Hari

You can use Conditonal formatting on the Format menu

Select O3:AG?
FormatConditonal formatting
Formula =
=($N3="yes")
click on the format button to pick a color

You can make one for "no" also



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Hari" wrote in message
...
Hi Ron,

Thanx a lot for ur code.

I can work with it.

Only one change I desired if possible. Usually this sheet might be
populated
by pasting data from another sheet

So many columns and rows of data could be pasted in this enmasse.

When u say "If Target.Cells.Count 1 Then Exit Sub"
I believe that would mean that if in column N, If Yes or NO is pasted
in 4
rows then it wouldnt work. ( For testing this I deleted that statement
and
tried the code and it didnt change the selection fill color
automatically)

Also the worksheet from which data is copied and pasted here would be
such
that we will be pasting data from column K through N or from column L
through N. For example if I copy K3:N7 and I paste it in K8 then the
code
doesnt work

So in above cases though the column N gets changed the code doesnt work.

In short, Is it possible for the "Target.Cells.Count " to work when the
count is greater than 1.

Please guide me.

( I have slightly modified to code to suit my requirements..Please find
it
pasted below my signature)

Regards,
Hari
India


Private Sub Worksheet_Change(ByVal Target As Range)
'If Target.Cells.Count 1 Then Exit Sub ' I have made this line
as a
comment by entering an apostrophe thinking that
If Target.Column = 14 And Target.Row 2 Then

If LCase(Target.Value) = "yes" Then
Range(Cells(Target.Row, "O"), Cells(Target.Row,
"AG")) _
.Interior.ColorIndex = 3 ' red

ElseIf LCase(Target.Value) = "no" Then

Range(Cells(Target.Row, "O"), Cells(Target.Row,
"AG")) _
.Interior.ColorIndex = xlColorIndexNone

ElseIf Target.Value < "" Then
MsgBox "Please enter only yes or no in Column
N"
Selection.ClearContents
Exit Sub

End If

End If


End Sub



"Ron de Bruin" wrote in message
...
Try this Hari

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Target.Column = 14 And Target.Row 2 Then
If LCase(Target.Value) = "yes" Then
Range(Cells(Target.Row, "O"), Cells(Target.Row, "AG")) _
.Interior.ColorIndex = 3 ' red
Else
Range(Cells(Target.Row, "O"), Cells(Target.Row, "AG")) _
.Interior.ColorIndex = xlColorIndexNone
End If
End If
End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Hari" wrote in message
...
Hi,

I have some a column N in a sheet called Raw Data. I have a cell
dropdown
saying "Yes" or "No".

It could be possible that this Yes or No data is pasted from some
other
workbook in to Column N.

I desire that if any row (starting from row no. 3 to used rows)
within
Column N has Yes then all columns from O to AG for that row change
to
Brown
fill color or
something like that. ( Actually if column N is a NO then its an
indication
that rest of the columns for that row will be blank)

I thought of a crude code like the following but not able to convert
it
in
to proper VB language.

Private Sub Worksheet_Change(ByVal Target As Range)
dim noofrows as integer
noofrows = ?? ' Dunno a crisp formula for calculating the noofrows
For i = 3 to noofrows
If cells(noofrows,14) = "Yes" then
Range("N"&noofrows&":AG"&noofrows).interior.colori ndex = 6 '
Actually I
know
that yellow is 6 But I prefer red or brown color to shade the
culprit
cells.
End if
Next noofrows
End Sub

Regards,
Hari
India

Hi Ron,

It worked like a charm.

Thanx for giving me some solid lessons in syntax.

(At the danger of being mean I have one humble request. I have one more
problem with syntax... I posted it yesterday with a subject
"To exceute a Macro when worksheet is deactivted and data is changed".
Please look over if possible. If not still a thousand thanks to you for
solving my worksheet_change problem)

Regards,
Hari
India


"Ron de Bruin" wrote in message
...
Hi Hari

Try this one

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
For Each cell In Target.Cells
If cell.Column = 14 And cell.Row 2 Then
If LCase(cell.Value) = "yes" Then
Range(Cells(cell.Row, "O"), Cells(cell.Row, "AG")) _
.Interior.ColorIndex = 3 ' red
Else
Range(Cells(cell.Row, "O"), Cells(cell.Row, "AG")) _
.Interior.ColorIndex = xlColorIndexNone
End If
End If
Next cell
End Sub

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Hari" wrote in message
...
Hi Ron,

Wanted to avoid conditional formatting as on pasting data etc it gets
overwritten ( also otherwise it gets inadvertently changed by the end
user..)

Hence wanted to set it up sort of permanently.

Sorry to bother u but just wanted to know if "Target" in the argument of
Worksheet_Change can take only one cell as range. I mean if lots of
cells
are pasted together it doesnt work...

Regards,
Hari
India
"Ron de Bruin" wrote in message
...
Hi Hari

You can use Conditonal formatting on the Format menu

Select O3:AG?
FormatConditonal formatting
Formula =
=($N3="yes")
click on the format button to pick a color

You can make one for "no" also



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Hari" wrote in message
...
Hi Ron,

Thanx a lot for ur code.

I can work with it.

Only one change I desired if possible. Usually this sheet might be
populated
by pasting data from another sheet

So many columns and rows of data could be pasted in this enmasse.

When u say "If Target.Cells.Count 1 Then Exit Sub"
I believe that would mean that if in column N, If Yes or NO is
pasted
in 4
rows then it wouldnt work. ( For testing this I deleted that
statement
and
tried the code and it didnt change the selection fill color
automatically)

Also the worksheet from which data is copied and pasted here would
be
such
that we will be pasting data from column K through N or from column
L
through N. For example if I copy K3:N7 and I paste it in K8 then the
code
doesnt work

So in above cases though the column N gets changed the code doesnt
work.

In short, Is it possible for the "Target.Cells.Count " to work when
the
count is greater than 1.

Please guide me.

( I have slightly modified to code to suit my requirements..Please
find
it
pasted below my signature)

Regards,
Hari
India


Private Sub Worksheet_Change(ByVal Target As Range)
'If Target.Cells.Count 1 Then Exit Sub ' I have made this
line
as a
comment by entering an apostrophe thinking that
If Target.Column = 14 And Target.Row 2 Then

If LCase(Target.Value) = "yes" Then
Range(Cells(Target.Row, "O"), Cells(Target.Row,
"AG")) _
.Interior.ColorIndex = 3 ' red

ElseIf LCase(Target.Value) = "no" Then

Range(Cells(Target.Row, "O"),
Cells(Target.Row,
"AG")) _
.Interior.ColorIndex = xlColorIndexNone

ElseIf Target.Value < "" Then
MsgBox "Please enter only yes or no in
Column
N"
Selection.ClearContents
Exit Sub

End If

End If


End Sub



"Ron de Bruin" wrote in message
...
Try this Hari

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Target.Column = 14 And Target.Row 2 Then
If LCase(Target.Value) = "yes" Then
Range(Cells(Target.Row, "O"), Cells(Target.Row, "AG"))
_
.Interior.ColorIndex = 3 ' red
Else
Range(Cells(Target.Row, "O"), Cells(Target.Row, "AG"))
_
.Interior.ColorIndex = xlColorIndexNone
End If
End If
End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Hari" wrote in message
...
Hi,

I have some a column N in a sheet called Raw Data. I have a cell
dropdown
saying "Yes" or "No".

It could be possible that this Yes or No data is pasted from
some
other
workbook in to Column N.

I desire that if any row (starting from row no. 3 to used rows)
within
Column N has Yes then all columns from O to AG for that row
change
to
Brown
fill color or
something like that. ( Actually if column N is a NO then its an
indication
that rest of the columns for that row will be blank)

I thought of a crude code like the following but not able to
convert
it
in
to proper VB language.

Private Sub Worksheet_Change(ByVal Target As Range)
dim noofrows as integer
noofrows = ?? ' Dunno a crisp formula for calculating the
noofrows
For i = 3 to noofrows
If cells(noofrows,14) = "Yes" then
Range("N"&noofrows&":AG"&noofrows).interior.colori ndex = 6 '
Actually I
know
that yellow is 6 But I prefer red or brown color to shade the
culprit
cells.
End if
Next noofrows
End Sub

Regards,
Hari
India

See your other thread

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Hari" wrote in message ...
Hi Ron,

It worked like a charm.

Thanx for giving me some solid lessons in syntax.

(At the danger of being mean I have one humble request. I have one more
problem with syntax... I posted it yesterday with a subject
"To exceute a Macro when worksheet is deactivted and data is changed".
Please look over if possible. If not still a thousand thanks to you for
solving my worksheet_change problem)

Regards,
Hari
India


"Ron de Bruin" wrote in message
...
Hi Hari

Try this one

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
For Each cell In Target.Cells
If cell.Column = 14 And cell.Row 2 Then
If LCase(cell.Value) = "yes" Then
Range(Cells(cell.Row, "O"), Cells(cell.Row, "AG")) _
.Interior.ColorIndex = 3 ' red
Else
Range(Cells(cell.Row, "O"), Cells(cell.Row, "AG")) _
.Interior.ColorIndex = xlColorIndexNone
End If
End If
Next cell
End Sub

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Hari" wrote in message
...
Hi Ron,

Wanted to avoid conditional formatting as on pasting data etc it gets
overwritten ( also otherwise it gets inadvertently changed by the end
user..)

Hence wanted to set it up sort of permanently.

Sorry to bother u but just wanted to know if "Target" in the argument of
Worksheet_Change can take only one cell as range. I mean if lots of
cells
are pasted together it doesnt work...

Regards,
Hari
India
"Ron de Bruin" wrote in message
...
Hi Hari

You can use Conditonal formatting on the Format menu

Select O3:AG?
FormatConditonal formatting
Formula =
=($N3="yes")
click on the format button to pick a color

You can make one for "no" also



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Hari" wrote in message
...
Hi Ron,

Thanx a lot for ur code.

I can work with it.

Only one change I desired if possible. Usually this sheet might be
populated
by pasting data from another sheet

So many columns and rows of data could be pasted in this enmasse.

When u say "If Target.Cells.Count 1 Then Exit Sub"
I believe that would mean that if in column N, If Yes or NO is
pasted
in 4
rows then it wouldnt work. ( For testing this I deleted that
statement
and
tried the code and it didnt change the selection fill color
automatically)

Also the worksheet from which data is copied and pasted here would
be
such
that we will be pasting data from column K through N or from column
L
through N. For example if I copy K3:N7 and I paste it in K8 then the
code
doesnt work

So in above cases though the column N gets changed the code doesnt
work.

In short, Is it possible for the "Target.Cells.Count " to work when
the
count is greater than 1.

Please guide me.

( I have slightly modified to code to suit my requirements..Please
find
it
pasted below my signature)

Regards,
Hari
India


Private Sub Worksheet_Change(ByVal Target As Range)
'If Target.Cells.Count 1 Then Exit Sub ' I have made this
line
as a
comment by entering an apostrophe thinking that
If Target.Column = 14 And Target.Row 2 Then

If LCase(Target.Value) = "yes" Then
Range(Cells(Target.Row, "O"), Cells(Target.Row,
"AG")) _
.Interior.ColorIndex = 3 ' red

ElseIf LCase(Target.Value) = "no" Then

Range(Cells(Target.Row, "O"),
Cells(Target.Row,
"AG")) _
.Interior.ColorIndex = xlColorIndexNone

ElseIf Target.Value < "" Then
MsgBox "Please enter only yes or no in
Column
N"
Selection.ClearContents
Exit Sub

End If

End If


End Sub



"Ron de Bruin" wrote in message
...
Try this Hari

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Target.Column = 14 And Target.Row 2 Then
If LCase(Target.Value) = "yes" Then
Range(Cells(Target.Row, "O"), Cells(Target.Row, "AG"))
_
.Interior.ColorIndex = 3 ' red
Else
Range(Cells(Target.Row, "O"), Cells(Target.Row, "AG"))
_
.Interior.ColorIndex = xlColorIndexNone
End If
End If
End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Hari" wrote in message
...
Hi,

I have some a column N in a sheet called Raw Data. I have a cell
dropdown
saying "Yes" or "No".

It could be possible that this Yes or No data is pasted from
some
other
workbook in to Column N.

I desire that if any row (starting from row no. 3 to used rows)
within
Column N has Yes then all columns from O to AG for that row
change
to
Brown
fill color or
something like that. ( Actually if column N is a NO then its an
indication
that rest of the columns for that row will be blank)

I thought of a crude code like the following but not able to
convert
it
in
to proper VB language.

Private Sub Worksheet_Change(ByVal Target As Range)
dim noofrows as integer
noofrows = ?? ' Dunno a crisp formula for calculating the
noofrows
For i = 3 to noofrows
If cells(noofrows,14) = "Yes" then
Range("N"&noofrows&":AG"&noofrows).interior.colori ndex = 6 '
Actually I
know
that yellow is 6 But I prefer red or brown color to shade the
culprit
cells.
End if
Next noofrows
End Sub

Regards,
Hari
India

Hi Ron,

Thanx a lot for your sportive spirit. May u have a great time.

Regards,
Hari
India


"Ron de Bruin" wrote in message
...
See your other thread

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Hari" wrote in message
...
Hi Ron,

It worked like a charm.

Thanx for giving me some solid lessons in syntax.

(At the danger of being mean I have one humble request. I have one more
problem with syntax... I posted it yesterday with a subject
"To exceute a Macro when worksheet is deactivted and data is changed".
Please look over if possible. If not still a thousand thanks to you for
solving my worksheet_change problem)

Regards,
Hari
India


"Ron de Bruin" wrote in message
...
Hi Hari

Try this one

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
For Each cell In Target.Cells
If cell.Column = 14 And cell.Row 2 Then
If LCase(cell.Value) = "yes" Then
Range(Cells(cell.Row, "O"), Cells(cell.Row, "AG")) _
.Interior.ColorIndex = 3 ' red
Else
Range(Cells(cell.Row, "O"), Cells(cell.Row, "AG")) _
.Interior.ColorIndex = xlColorIndexNone
End If
End If
Next cell
End Sub

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Hari" wrote in message
...
Hi Ron,

Wanted to avoid conditional formatting as on pasting data etc it
gets
overwritten ( also otherwise it gets inadvertently changed by the
end
user..)

Hence wanted to set it up sort of permanently.

Sorry to bother u but just wanted to know if "Target" in the
argument of
Worksheet_Change can take only one cell as range. I mean if lots of
cells
are pasted together it doesnt work...

Regards,
Hari
India
"Ron de Bruin" wrote in message
...
Hi Hari

You can use Conditonal formatting on the Format menu

Select O3:AG?
FormatConditonal formatting
Formula =
=($N3="yes")
click on the format button to pick a color

You can make one for "no" also



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Hari" wrote in message
...
Hi Ron,

Thanx a lot for ur code.

I can work with it.

Only one change I desired if possible. Usually this sheet might
be
populated
by pasting data from another sheet

So many columns and rows of data could be pasted in this
enmasse.

When u say "If Target.Cells.Count 1 Then Exit Sub"
I believe that would mean that if in column N, If Yes or NO is
pasted
in 4
rows then it wouldnt work. ( For testing this I deleted that
statement
and
tried the code and it didnt change the selection fill color
automatically)

Also the worksheet from which data is copied and pasted here
would
be
such
that we will be pasting data from column K through N or from
column
L
through N. For example if I copy K3:N7 and I paste it in K8 then
the
code
doesnt work

So in above cases though the column N gets changed the code
doesnt
work.

In short, Is it possible for the "Target.Cells.Count " to work
when
the
count is greater than 1.

Please guide me.

( I have slightly modified to code to suit my
requirements..Please
find
it
pasted below my signature)

Regards,
Hari
India


Private Sub Worksheet_Change(ByVal Target As Range)
'If Target.Cells.Count 1 Then Exit Sub ' I have made
this
line
as a
comment by entering an apostrophe thinking that
If Target.Column = 14 And Target.Row 2 Then

If LCase(Target.Value) = "yes" Then
Range(Cells(Target.Row, "O"),
Cells(Target.Row,
"AG")) _
.Interior.ColorIndex = 3 ' red

ElseIf LCase(Target.Value) = "no" Then

Range(Cells(Target.Row, "O"),
Cells(Target.Row,
"AG")) _
.Interior.ColorIndex = xlColorIndexNone

ElseIf Target.Value < "" Then
MsgBox "Please enter only yes or no in
Column
N"
Selection.ClearContents
Exit Sub

End If

End If


End Sub



"Ron de Bruin" wrote in message
...
Try this Hari

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Target.Column = 14 And Target.Row 2 Then
If LCase(Target.Value) = "yes" Then
Range(Cells(Target.Row, "O"), Cells(Target.Row,
"AG"))
_
.Interior.ColorIndex = 3 ' red
Else
Range(Cells(Target.Row, "O"), Cells(Target.Row,
"AG"))
_
.Interior.ColorIndex = xlColorIndexNone
End If
End If
End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Hari" wrote in message
...
Hi,

I have some a column N in a sheet called Raw Data. I have a
cell
dropdown
saying "Yes" or "No".

It could be possible that this Yes or No data is pasted from
some
other
workbook in to Column N.

I desire that if any row (starting from row no. 3 to used
rows)
within
Column N has Yes then all columns from O to AG for that row
change
to
Brown
fill color or
something like that. ( Actually if column N is a NO then its
an
indication
that rest of the columns for that row will be blank)

I thought of a crude code like the following but not able to
convert
it
in
to proper VB language.

Private Sub Worksheet_Change(ByVal Target As Range)
dim noofrows as integer
noofrows = ?? ' Dunno a crisp formula for calculating the
noofrows
For i = 3 to noofrows
If cells(noofrows,14) = "Yes" then
Range("N"&noofrows&":AG"&noofrows).interior.colori ndex = 6 '
Actually I
know
that yellow is 6 But I prefer red or brown color to shade
the
culprit
cells.
End if
Next noofrows
End Sub

Regards,
Hari
India