Worksheet_change - Murdering syntax

[Hari]

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.colorindex = 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

 

[Ron de Bruin]

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

 

[Hari]

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]

Hi Hari

You can use Conditonal formatting on the Format menu

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

You can make one for "no" also

 

[Hari]

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

 

[Hari]

Hi Ron,

Very strange. I posted the message below 10 or so minutes back from Outlook
and when I checked after a minute or so I got a message that my post is no
longer available in the server ( The one we get when somebody posts
objectionable stuff in Newsgroups..) Any idea why did it happened?

Anyway, back to original ..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]

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

 

[Hari]

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]

See your other thread

 

[Hari]

Hi Ron,

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

Regards,
Hari
India