More than 6 conditional Formats....VBA Coding Advise please

[Dermot]

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

 

[keithl816]

Take a look at this link

http://www.excelforum.com/showthread.php?t=497871

 

[JulieD]

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]

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

 

[Dermot]

Thanks for the link Keith.
Interesting stuff
Thanks

 

[Dermot]

Hi Julie,
What line of code would I add to remove the colour formatting to it's
original blank appearance with no text within a row.......assumming I
included an option in the validation list "Undo"?
Thanks
Dermot

 

[Dermot]

Thanks for the reply R.VENKATARAMAN
Please advise....
Wouldn't this just delete conditional formating condition 1?
I am looking to include an option in the validation list to clear the
formatting carried out by the code......I don't want to pemanently remove any
formatting.
Thanks