Hi Julie,
This is great.
I had found a case select code else where, but wasn't too sure how to edit
to the "Yes" etc conditions that I would like to use.
I am reading through the Susann Novalis VBA book which is great, but this
code is more advanced than the level I am at.
Can you suggest any other VBA learning sources for Excel?
Thanks again
Dermot

"JulieD" wrote:

Hi Dermot

If i understand your question correctly then the following code should do
what you want (you'll have to edit the colours as required).
--------------------
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Err_Handler

If Not Intersect(Target, Range("K1:K20")) Is Nothing Then
Application.EnableEvents = False
i = Target.Row
Select Case Target.Value
Case "Yes"
Range("A" & i & ":M" & i).Interior.ColorIndex = 6
Case "No"
Range("A" & i & ":M" & i).Interior.ColorIndex = 12
Case "W"
Range("A" & i & ":M" & i).Interior.ColorIndex = 18
Case "X"
Range("A" & i & ":M" & i).Interior.ColorIndex = 22
Case "Y"
Range("A" & i & ":M" & i).Interior.ColorIndex = 26
Case "Z"
Range("A" & i & ":M" & i).Interior.ColorIndex = 30
Case Else
Range("A" & i & ":M" & i).Interior.ColorIndex = xlNone
End Select
End If

Err_Handler:
Application.EnableEvents = True
End Sub

--
note, this needs to go on the sheet module of where your range is (right
mouse click on the appropriate sheet tab and choose view / code ... copy &
paste the code in there).

hope this helps.

--
Cheers
JulieD


julied_ng at hctsReMoVeThIs dot net dot au


"Dermot" wrote:

If I have a range of cells A1:M20
Column K has a validation list with 6 Options....Yes, No, W, X, Y, Z

The conditional formatting is limited to 3 conditions.

Can anyone provide code that would permit the colour of the cells in each
row in the range A1 to M20 when the appropriate option is selected from the
relevant cell in column K.

I.E. I want Row range A1:M1 cells to change to say Yellow when "Yes"
selected from the list in K1 ......or say red if "No" ( and other colours
for W,X Y,Z)

An example of the coding would be appreciated , if it's possible?
Thanks