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 quot;Yesquot;
selected from the list in K1 ......or say red if quot;Noquot; ( 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
www.excelforum.com/showthread.php?t=497871--
keithl816
------------------------------------------------------------------------
keithl816's Profile: www.excelforum.com/member.php...oamp;userid=21287
View this thread: www.excelforum.com/showthread...hreadid=498558Hi 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(quot;K1:K20quot;)) Is Nothing Then
Application.EnableEvents = False
i = Target.Row
Select Case Target.Value
Case quot;Yesquot;
Range(quot;Aquot; amp; i amp; quot;:Mquot; amp; i).Interior.ColorIndex = 6
Case quot;Noquot;
Range(quot;Aquot; amp; i amp; quot;:Mquot; amp; i).Interior.ColorIndex = 12
Case quot;Wquot;
Range(quot;Aquot; amp; i amp; quot;:Mquot; amp; i).Interior.ColorIndex = 18
Case quot;Xquot;
Range(quot;Aquot; amp; i amp; quot;:Mquot; amp; i).Interior.ColorIndex = 22
Case quot;Yquot;
Range(quot;Aquot; amp; i amp; quot;:Mquot; amp; i).Interior.ColorIndex = 26
Case quot;Zquot;
Range(quot;Aquot; amp; i amp; quot;:Mquot; amp; i).Interior.ColorIndex = 30
Case Else
Range(quot;Aquot; amp; i amp; quot;:Mquot; amp; 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 amp;
paste the code in there).
hope this helps.
--
Cheers
JulieDjulied_ng at hctsReMoVeThIs dot net dot auquot;Dermotquot; wrote:
gt; If I have a range of cells A1:M20
gt; Column K has a validation list with 6 Options....Yes, No, W, X, Y, Z
gt;
gt; The conditional formatting is limited to 3 conditions.
gt;
gt; Can anyone provide code that would permit the colour of the cells in each
gt; row in the range A1 to M20 when the appropriate option is selected from the
gt; relevant cell in column K.
gt;
gt; I.E. I want Row range A1:M1 cells to change to say Yellow when quot;Yesquot;
gt; selected from the list in K1 ......or say red if quot;Noquot; ( and other colours
gt; for W,X Y,Z)
gt;
gt; An example of the coding would be appreciated , if it's possible?
gt; Thanks
gt;
Hi Julie,
This is great.
I had found a case select code else where, but wasn't too sure how to edit
to the quot;Yesquot; 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
quot;JulieDquot; wrote:
gt; Hi Dermot
gt;
gt; If i understand your question correctly then the following code should do
gt; what you want (you'll have to edit the colours as required).
gt; --------------------
gt; Private Sub Worksheet_Change(ByVal Target As Range)
gt; On Error GoTo Err_Handler
gt;
gt; If Not Intersect(Target, Range(quot;K1:K20quot;)) Is Nothing Then
gt; Application.EnableEvents = False
gt; i = Target.Row
gt; Select Case Target.Value
gt; Case quot;Yesquot;
gt; Range(quot;Aquot; amp; i amp; quot;:Mquot; amp; i).Interior.ColorIndex = 6
gt; Case quot;Noquot;
gt; Range(quot;Aquot; amp; i amp; quot;:Mquot; amp; i).Interior.ColorIndex = 12
gt; Case quot;Wquot;
gt; Range(quot;Aquot; amp; i amp; quot;:Mquot; amp; i).Interior.ColorIndex = 18
gt; Case quot;Xquot;
gt; Range(quot;Aquot; amp; i amp; quot;:Mquot; amp; i).Interior.ColorIndex = 22
gt; Case quot;Yquot;
gt; Range(quot;Aquot; amp; i amp; quot;:Mquot; amp; i).Interior.ColorIndex = 26
gt; Case quot;Zquot;
gt; Range(quot;Aquot; amp; i amp; quot;:Mquot; amp; i).Interior.ColorIndex = 30
gt; Case Else
gt; Range(quot;Aquot; amp; i amp; quot;:Mquot; amp; i).Interior.ColorIndex = xlNone
gt; End Select
gt; End If
gt;
gt; Err_Handler:
gt; Application.EnableEvents = True
gt; End Sub
gt;
gt; --
gt; note, this needs to go on the sheet module of where your range is (right
gt; mouse click on the appropriate sheet tab and choose view / code ... copy amp;
gt; paste the code in there).
gt;
gt; hope this helps.
gt;
gt; --
gt; Cheers
gt; JulieD
gt;
gt;
gt; julied_ng at hctsReMoVeThIs dot net dot au
gt;
gt;
gt; quot;Dermotquot; wrote:
gt;
gt; gt; If I have a range of cells A1:M20
gt; gt; Column K has a validation list with 6 Options....Yes, No, W, X, Y, Z
gt; gt;
gt; gt; The conditional formatting is limited to 3 conditions.
gt; gt;
gt; gt; Can anyone provide code that would permit the colour of the cells in each
gt; gt; row in the range A1 to M20 when the appropriate option is selected from the
gt; gt; relevant cell in column K.
gt; gt;
gt; gt; I.E. I want Row range A1:M1 cells to change to say Yellow when quot;Yesquot;
gt; gt; selected from the list in K1 ......or say red if quot;Noquot; ( and other colours
gt; gt; for W,X Y,Z)
gt; gt;
gt; gt; An example of the coding would be appreciated , if it's possible?
gt; gt; Thanks
gt; gt;
Thanks for the link Keith.
Interesting stuff
Thanks
quot;keithl816quot; wrote:
gt;
gt; Take a look at this link
gt;
gt; www.excelforum.com/showthread.php?t=497871
gt;
gt;
gt; --
gt; keithl816
gt; ------------------------------------------------------------------------
gt; keithl816's Profile: www.excelforum.com/member.php...oamp;userid=21287
gt; View this thread: www.excelforum.com/showthread...hreadid=498558
gt;
gt;
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 quot;Undoquot;?
Thanks
Dermot
quot;JulieDquot; wrote:
gt; Hi Dermot
gt;
gt; If i understand your question correctly then the following code should do
gt; what you want (you'll have to edit the colours as required).
gt; --------------------
gt; Private Sub Worksheet_Change(ByVal Target As Range)
gt; On Error GoTo Err_Handler
gt;
gt; If Not Intersect(Target, Range(quot;K1:K20quot;)) Is Nothing Then
gt; Application.EnableEvents = False
gt; i = Target.Row
gt; Select Case Target.Value
gt; Case quot;Yesquot;
gt; Range(quot;Aquot; amp; i amp; quot;:Mquot; amp; i).Interior.ColorIndex = 6
gt; Case quot;Noquot;
gt; Range(quot;Aquot; amp; i amp; quot;:Mquot; amp; i).Interior.ColorIndex = 12
gt; Case quot;Wquot;
gt; Range(quot;Aquot; amp; i amp; quot;:Mquot; amp; i).Interior.ColorIndex = 18
gt; Case quot;Xquot;
gt; Range(quot;Aquot; amp; i amp; quot;:Mquot; amp; i).Interior.ColorIndex = 22
gt; Case quot;Yquot;
gt; Range(quot;Aquot; amp; i amp; quot;:Mquot; amp; i).Interior.ColorIndex = 26
gt; Case quot;Zquot;
gt; Range(quot;Aquot; amp; i amp; quot;:Mquot; amp; i).Interior.ColorIndex = 30
gt; Case Else
gt; Range(quot;Aquot; amp; i amp; quot;:Mquot; amp; i).Interior.ColorIndex = xlNone
gt; End Select
gt; End If
gt;
gt; Err_Handler:
gt; Application.EnableEvents = True
gt; End Sub
gt;
gt; --
gt; note, this needs to go on the sheet module of where your range is (right
gt; mouse click on the appropriate sheet tab and choose view / code ... copy amp;
gt; paste the code in there).
gt;
gt; hope this helps.
gt;
gt; --
gt; Cheers
gt; JulieD
gt;
gt;
gt; julied_ng at hctsReMoVeThIs dot net dot au
gt;
gt;
gt; quot;Dermotquot; wrote:
gt;
gt; gt; If I have a range of cells A1:M20
gt; gt; Column K has a validation list with 6 Options....Yes, No, W, X, Y, Z
gt; gt;
gt; gt; The conditional formatting is limited to 3 conditions.
gt; gt;
gt; gt; Can anyone provide code that would permit the colour of the cells in each
gt; gt; row in the range A1 to M20 when the appropriate option is selected from the
gt; gt; relevant cell in column K.
gt; gt;
gt; gt; I.E. I want Row range A1:M1 cells to change to say Yellow when quot;Yesquot;
gt; gt; selected from the list in K1 ......or say red if quot;Noquot; ( and other colours
gt; gt; for W,X Y,Z)
gt; gt;
gt; gt; An example of the coding would be appreciated , if it's possible?
gt; gt; Thanks
gt; gt;
foramt menu-conditional formatting-delete-option 1-ok
quot;Dermotquot; gt; wrote in message
...
gt; Hi Julie,
gt; What line of code would I add to remove the colour formatting to it's
gt; original blank appearance with no text within a row.......assumming I
gt; included an option in the validation list quot;Undoquot;?
gt; Thanks
gt; Dermot
gt;
gt; quot;JulieDquot; wrote:
gt;
gt; gt; Hi Dermot
gt; gt;
gt; gt; If i understand your question correctly then the following code should
do
gt; gt; what you want (you'll have to edit the colours as required).
gt; gt; --------------------
gt; gt; Private Sub Worksheet_Change(ByVal Target As Range)
gt; gt; On Error GoTo Err_Handler
gt; gt;
gt; gt; If Not Intersect(Target, Range(quot;K1:K20quot;)) Is Nothing Then
gt; gt; Application.EnableEvents = False
gt; gt; i = Target.Row
gt; gt; Select Case Target.Value
gt; gt; Case quot;Yesquot;
gt; gt; Range(quot;Aquot; amp; i amp; quot;:Mquot; amp; i).Interior.ColorIndex = 6
gt; gt; Case quot;Noquot;
gt; gt; Range(quot;Aquot; amp; i amp; quot;:Mquot; amp; i).Interior.ColorIndex = 12
gt; gt; Case quot;Wquot;
gt; gt; Range(quot;Aquot; amp; i amp; quot;:Mquot; amp; i).Interior.ColorIndex = 18
gt; gt; Case quot;Xquot;
gt; gt; Range(quot;Aquot; amp; i amp; quot;:Mquot; amp; i).Interior.ColorIndex = 22
gt; gt; Case quot;Yquot;
gt; gt; Range(quot;Aquot; amp; i amp; quot;:Mquot; amp; i).Interior.ColorIndex = 26
gt; gt; Case quot;Zquot;
gt; gt; Range(quot;Aquot; amp; i amp; quot;:Mquot; amp; i).Interior.ColorIndex = 30
gt; gt; Case Else
gt; gt; Range(quot;Aquot; amp; i amp; quot;:Mquot; amp; i).Interior.ColorIndex = xlNone
gt; gt; End Select
gt; gt; End If
gt; gt;
gt; gt; Err_Handler:
gt; gt; Application.EnableEvents = True
gt; gt; End Sub
gt; gt;
gt; gt; --
gt; gt; note, this needs to go on the sheet module of where your range is (right
gt; gt; mouse click on the appropriate sheet tab and choose view / code ... copy
amp;
gt; gt; paste the code in there).
gt; gt;
gt; gt; hope this helps.
gt; gt;
gt; gt; --
gt; gt; Cheers
gt; gt; JulieD
gt; gt;
gt; gt;
gt; gt; julied_ng at hctsReMoVeThIs dot net dot au
gt; gt;
gt; gt;
gt; gt; quot;Dermotquot; wrote:
gt; gt;
gt; gt; gt; If I have a range of cells A1:M20
gt; gt; gt; Column K has a validation list with 6 Options....Yes, No, W, X, Y, Z
gt; gt; gt;
gt; gt; gt; The conditional formatting is limited to 3 conditions.
gt; gt; gt;
gt; gt; gt; Can anyone provide code that would permit the colour of the cells in
each
gt; gt; gt; row in the range A1 to M20 when the appropriate option is selected
from the
gt; gt; gt; relevant cell in column K.
gt; gt; gt;
gt; gt; gt; I.E. I want Row range A1:M1 cells to change to say Yellow when quot;Yesquot;
gt; gt; gt; selected from the list in K1 ......or say red if quot;Noquot; ( and other
colours
gt; gt; gt; for W,X Y,Z)
gt; gt; gt;
gt; gt; gt; An example of the coding would be appreciated , if it's possible?
gt; gt; gt; Thanks
gt; gt; gt;
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
quot;quot; wrote:
gt; foramt menu-conditional formatting-delete-option 1-ok
gt;
gt;
gt;
gt; quot;Dermotquot; gt; wrote in message
gt; ...
gt; gt; Hi Julie,
gt; gt; What line of code would I add to remove the colour formatting to it's
gt; gt; original blank appearance with no text within a row.......assumming I
gt; gt; included an option in the validation list quot;Undoquot;?
gt; gt; Thanks
gt; gt; Dermot
gt; gt;
gt; gt; quot;JulieDquot; wrote:
gt; gt;
gt; gt; gt; Hi Dermot
gt; gt; gt;
gt; gt; gt; If i understand your question correctly then the following code should
gt; do
gt; gt; gt; what you want (you'll have to edit the colours as required).
gt; gt; gt; --------------------
gt; gt; gt; Private Sub Worksheet_Change(ByVal Target As Range)
gt; gt; gt; On Error GoTo Err_Handler
gt; gt; gt;
gt; gt; gt; If Not Intersect(Target, Range(quot;K1:K20quot;)) Is Nothing Then
gt; gt; gt; Application.EnableEvents = False
gt; gt; gt; i = Target.Row
gt; gt; gt; Select Case Target.Value
gt; gt; gt; Case quot;Yesquot;
gt; gt; gt; Range(quot;Aquot; amp; i amp; quot;:Mquot; amp; i).Interior.ColorIndex = 6
gt; gt; gt; Case quot;Noquot;
gt; gt; gt; Range(quot;Aquot; amp; i amp; quot;:Mquot; amp; i).Interior.ColorIndex = 12
gt; gt; gt; Case quot;Wquot;
gt; gt; gt; Range(quot;Aquot; amp; i amp; quot;:Mquot; amp; i).Interior.ColorIndex = 18
gt; gt; gt; Case quot;Xquot;
gt; gt; gt; Range(quot;Aquot; amp; i amp; quot;:Mquot; amp; i).Interior.ColorIndex = 22
gt; gt; gt; Case quot;Yquot;
gt; gt; gt; Range(quot;Aquot; amp; i amp; quot;:Mquot; amp; i).Interior.ColorIndex = 26
gt; gt; gt; Case quot;Zquot;
gt; gt; gt; Range(quot;Aquot; amp; i amp; quot;:Mquot; amp; i).Interior.ColorIndex = 30
gt; gt; gt; Case Else
gt; gt; gt; Range(quot;Aquot; amp; i amp; quot;:Mquot; amp; i).Interior.ColorIndex = xlNone
gt; gt; gt; End Select
gt; gt; gt; End If
gt; gt; gt;
gt; gt; gt; Err_Handler:
gt; gt; gt; Application.EnableEvents = True
gt; gt; gt; End Sub
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt; note, this needs to go on the sheet module of where your range is (right
gt; gt; gt; mouse click on the appropriate sheet tab and choose view / code ... copy
gt; amp;
gt; gt; gt; paste the code in there).
gt; gt; gt;
gt; gt; gt; hope this helps.
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt; Cheers
gt; gt; gt; JulieD
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; julied_ng at hctsReMoVeThIs dot net dot au
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; quot;Dermotquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; If I have a range of cells A1:M20
gt; gt; gt; gt; Column K has a validation list with 6 Options....Yes, No, W, X, Y, Z
gt; gt; gt; gt;
gt; gt; gt; gt; The conditional formatting is limited to 3 conditions.
gt; gt; gt; gt;
gt; gt; gt; gt; Can anyone provide code that would permit the colour of the cells in
gt; each
gt; gt; gt; gt; row in the range A1 to M20 when the appropriate option is selected
gt; from the
gt; gt; gt; gt; relevant cell in column K.
gt; gt; gt; gt;
gt; gt; gt; gt; I.E. I want Row range A1:M1 cells to change to say Yellow when quot;Yesquot;
gt; gt; gt; gt; selected from the list in K1 ......or say red if quot;Noquot; ( and other
gt; colours
gt; gt; gt; gt; for W,X Y,Z)
gt; gt; gt; gt;
gt; gt; gt; gt; An example of the coding would be appreciated , if it's possible?
gt; gt; gt; gt; Thanks
gt; gt; gt; gt;
gt;
gt;
gt;
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:=quot;5quot;
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
quot;Dermotquot; gt; wrote in message
...
gt; Thanks for the reply R.VENKATARAMAN
gt; Please advise....
gt; Wouldn't this just delete conditional formating condition 1?
gt; I am looking to include an option in the validation list to clear the
gt; formatting carried out by the code......I don't want to pemanently remove
any
gt; formatting.
gt; Thanks
gt;
gt; quot;quot; wrote:
gt;
gt; gt; foramt menu-conditional formatting-delete-option 1-ok
gt; gt;
gt; gt;
gt; gt;
gt; gt; quot;Dermotquot; gt; wrote in message
gt; gt; ...
gt; gt; gt; Hi Julie,
gt; gt; gt; What line of code would I add to remove the colour formatting to it's
gt; gt; gt; original blank appearance with no text within a row.......assumming I
gt; gt; gt; included an option in the validation list quot;Undoquot;?
gt; gt; gt; Thanks
gt; gt; gt; Dermot
gt; gt; gt;
gt; gt; gt; quot;JulieDquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; Hi Dermot
gt; gt; gt; gt;
gt; gt; gt; gt; If i understand your question correctly then the following code
should
gt; gt; do
gt; gt; gt; gt; what you want (you'll have to edit the colours as required).
gt; gt; gt; gt; --------------------
gt; gt; gt; gt; Private Sub Worksheet_Change(ByVal Target As Range)
gt; gt; gt; gt; On Error GoTo Err_Handler
gt; gt; gt; gt;
gt; gt; gt; gt; If Not Intersect(Target, Range(quot;K1:K20quot;)) Is Nothing Then
gt; gt; gt; gt; Application.EnableEvents = False
gt; gt; gt; gt; i = Target.Row
gt; gt; gt; gt; Select Case Target.Value
gt; gt; gt; gt; Case quot;Yesquot;
gt; gt; gt; gt; Range(quot;Aquot; amp; i amp; quot;:Mquot; amp; i).Interior.ColorIndex = 6
gt; gt; gt; gt; Case quot;Noquot;
gt; gt; gt; gt; Range(quot;Aquot; amp; i amp; quot;:Mquot; amp; i).Interior.ColorIndex = 12
gt; gt; gt; gt; Case quot;Wquot;
gt; gt; gt; gt; Range(quot;Aquot; amp; i amp; quot;:Mquot; amp; i).Interior.ColorIndex = 18
gt; gt; gt; gt; Case quot;Xquot;
gt; gt; gt; gt; Range(quot;Aquot; amp; i amp; quot;:Mquot; amp; i).Interior.ColorIndex = 22
gt; gt; gt; gt; Case quot;Yquot;
gt; gt; gt; gt; Range(quot;Aquot; amp; i amp; quot;:Mquot; amp; i).Interior.ColorIndex = 26
gt; gt; gt; gt; Case quot;Zquot;
gt; gt; gt; gt; Range(quot;Aquot; amp; i amp; quot;:Mquot; amp; i).Interior.ColorIndex = 30
gt; gt; gt; gt; Case Else
gt; gt; gt; gt; Range(quot;Aquot; amp; i amp; quot;:Mquot; amp; i).Interior.ColorIndex =
xlNone
gt; gt; gt; gt; End Select
gt; gt; gt; gt; End If
gt; gt; gt; gt;
gt; gt; gt; gt; Err_Handler:
gt; gt; gt; gt; Application.EnableEvents = True
gt; gt; gt; gt; End Sub
gt; gt; gt; gt;
gt; gt; gt; gt; --
gt; gt; gt; gt; note, this needs to go on the sheet module of where your range is
(right
gt; gt; gt; gt; mouse click on the appropriate sheet tab and choose view / code ...
copy
gt; gt; amp;
gt; gt; gt; gt; paste the code in there).
gt; gt; gt; gt;
gt; gt; gt; gt; hope this helps.
gt; gt; gt; gt;
gt; gt; gt; gt; --
gt; gt; gt; gt; Cheers
gt; gt; gt; gt; JulieD
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; julied_ng at hctsReMoVeThIs dot net dot au
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; quot;Dermotquot; wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; gt; If I have a range of cells A1:M20
gt; gt; gt; gt; gt; Column K has a validation list with 6 Options....Yes, No, W, X, Y,
Z
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; The conditional formatting is limited to 3 conditions.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Can anyone provide code that would permit the colour of the cells
in
gt; gt; each
gt; gt; gt; gt; gt; row in the range A1 to M20 when the appropriate option is selected
gt; gt; from the
gt; gt; gt; gt; gt; relevant cell in column K.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; I.E. I want Row range A1:M1 cells to change to say Yellow when
quot;Yesquot;
gt; gt; gt; gt; gt; selected from the list in K1 ......or say red if quot;Noquot; ( and other
gt; gt; colours
gt; gt; gt; gt; gt; for W,X Y,Z)
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; An example of the coding would be appreciated , if it's possible?
gt; gt; gt; gt; gt; Thanks
gt; gt; gt; gt; gt;
gt; gt;
gt; gt;
gt; gt;
留言列表
