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

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

Take a look at this link

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


--
keithl816
------------------------------------------------------------------------
keithl816's Profile: http://www.excelforum.com/member.php...o&userid=21287
View this thread: http://www.excelforum.com/showthread...hreadid=498558

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

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

Thanks for the link Keith.
Interesting stuff
Thanks

"keithl816" wrote:


Take a look at this link

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


--
keithl816
------------------------------------------------------------------------
keithl816's Profile: http://www.excelforum.com/member.php...o&userid=21287
View this thread: http://www.excelforum.com/showthread...hreadid=498558

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

"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

foramt menu-conditional formatting-delete-option 1-ok



"Dermot" wrote in message
...
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

"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

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

"" wrote:

foramt menu-conditional formatting-delete-option 1-ok



"Dermot" wrote in message
...
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

"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

iam avaguely thinking
you can have two command buttons
one will have the code

Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
Formula1:="5"
Selection.FormatConditions(1).Font.ColorIndex = 46

anohter button will have the code

Selection.FormatConditions.Delete

try somethin like that . insted of command buttons you can even have two
option button

"Dermot" wrote in message
...
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

"" wrote:

foramt menu-conditional formatting-delete-option 1-ok



"Dermot" wrote in message
...
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

"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