| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
More than 6 conditional Formats....VBA Coding Advise please
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 |
|
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
More than 6 conditional Formats....VBA Coding Advise please
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 |
|
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
More than 6 conditional Formats....VBA Coding Advise please
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 |
|
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
More than 6 conditional Formats....VBA Coding Advise please
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 |
|
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
More than 6 conditional Formats....VBA Coding Advise please
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 |
|
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
More than 6 conditional Formats....VBA Coding Advise please
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 |
|
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
More than 6 conditional Formats....VBA Coding Advise please
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 |
|
#9
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
More than 6 conditional Formats....VBA Coding Advise please
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Number of Conditional Formats | Excel Worksheet Functions | |||
| list all conditional formats | Excel Worksheet Functions | |||
| how do I apply more than 3 conditional formats in excel | Excel Discussion (Misc queries) | |||
| How do I set more than 3 conditional formats in Excel | Excel Discussion (Misc queries) | |||
| Conditional Formats in Excel | Excel Worksheet Functions | |||
Linear Mode
