it did not work ?

[saud]

Thanks again. I really appreciate how helpful you people are.

I was looking for a way to be able to color the whole sheet unti
thanks to David McRitchie directed me to a reply to a question o
Possible to Apply More than Three Conditional Formats to a column?

There was a color-code
So I did as directed, Right click on the Excel sheet tab and selec
'View Code'
I copied the following into the sheet that just opened in the
VBA editor.


Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Count <> 1 Then Exit Sub 'action only if 1
cell is selected

If Target.Column <> 1 Then Exit Sub 'action only if
cell changed in column A

Select Case Target.Value 'evaluate the number that
you just entered

Case Is = 1
Target.EntireRow.Interior.ColorIndex = 3 'red

Case Is = 2
Target.EntireRow.Interior.ColorIndex = 38 'pink

Case Is = 3
Target.EntireRow.Interior.ColorIndex = 4 'green

Case Is = 4
Target.EntireRow.Interior.ColorIndex = 6 'yellow

Case Is = 5
Target.EntireRow.Interior.ColorIndex = 8 'majenta

Case Is = 6
Target.EntireRow.Interior.ColorIndex = 5 'blue

Case Else 'none of the above numbers
Exit Sub

End Select

End Sub



but unfortunately when I enter the number 1 in column A I get a massag
in the VBA editor saying (compile error – syntax error ) and when
press OK on the system widow it shades the first line which say
*Private Sub Worksheet_Change(ByVal Target As Range* with yello
color and this line *cell is selected* with blue color and it didn’
change the cell color ? So please help me,

What is the solution to this?

Thanks

 

[Debra Dalgleish]

The text "cell is selected" is part of the comment from the preceding
line. Remove the line break from that comment, and from the next two
comments in the code. Here's the section of code with the comments
placed on separate lines:

'action only if 1 cell is selected
If Target.Count <> 1 Then Exit Sub

'action only if cell changed in column A
If Target.Column <> 1 Then Exit Sub

'evaluate the number that you just entered
Select Case Target.Value
Case Is = 1

 

[Jerry W. Lewis]

Your program works fine for me after I unwrap the lines that have
trailing comments. This was verified in both Excel 2000 and Excel XP.

Note that the code must be on the Worksheet code page, not in a module
or the Workbook code page; however I could not reproduce the error you
report by placing the code in either incorrect location.

The "Exit Sub" under "Case Else" is unnecessary, unless your real
program has additional statements following "End Select".

Jerry

 

[Don Guillett]

1. How do you look for "It did not work" in the archives. Please use
meaninful subject lines such as conditional formatting with more than 3
conditions.
2. Your code worked, as written, in xl2002. Perhaps you had the comment
lines on more than one line???
When pasted, mine showed red until I changed that.
3. Try this instead. Better is using 1,2,3,4,5
'=======
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim x As Integer
If Target.Column <> 1 _
Or Target < 1 Or Target > 6 Then Exit Sub
x = Choose(Target, 3, 38, 4, 6, 8, 5)
Target.EntireRow.Interior.ColorIndex = x
end sub
'========

 

[Don Guillett]

Forgot to mention that you should stay in the original thread and then your
subject line would have been OK. Well, not OK but acceptable.