Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting Code
Need help creating the code for adding conditional formatting to cell ranges €¦
For range (Axx:Ryy), I need the following conditional formatting conditions created: 1 €“ Shade row grey if column "K" value = "Completed" 2 €“ Shade row grey if column "K" value = "Cancelled" In addition, column "I" needs to have a 3rd condition: 3 €“ Make column "I" value red font if condition met (formula =$Jx < 3) I recorded a macro for creating the conditions & got the following code that I can copy to cells. Just not sure how to generalize it so it will not reference cell $K701 below (it needs to reference columns K & J but apply to all rows in the range): Range(strI_Range).Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=$K701 = ""Completed""" Selection.FormatConditions(1).Interior.ColorIndex = 33 Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=$K701 = ""Cancelled""" Selection.FormatConditions(2).Interior.ColorIndex = 33 ' 3rd condition in cond fmtg sub for column I only Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$J701 < 3" With Selection.FormatConditions(3).Font .Bold = True .Italic = False .ColorIndex = 3 End With If there is a more elegant way to do this, please let me know. Right now I have this as 2 subs (one for col I & the other for the other cells in the range). I also have a Click Event in Sheet 1 that shades a column for different values. Not sure how to combine them (if that's possible and/or if it is worth doing). Thanks for your help! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting Code
With Range(strI_Range).EntireRow
.FormatConditions.Delete .FormatConditions.Add Type:=xlExpression, _ Formula1:="=$K" & ActiveCell.Row & "=""Completed""" .FormatConditions(1).Interior.ColorIndex = 33 .FormatConditions.Add Type:=xlExpression, _ Formula1:="=$K" & ActiveCell.Row & "=""Cancelled""" .FormatConditions(2).Interior.ColorIndex = 33 .FormatConditions.Add Type:=xlExpression, _ Formula1:="=AND(COLUMN()=9,$J" & ActiveCell.Row & "<=3)" With .FormatConditions(3).Font .Bold = True .Italic = False .ColorIndex = 3 End With End With -- __________________________________ HTH Bob "JoAnn" wrote in message ... Need help creating the code for adding conditional formatting to cell ranges . For range (Axx:Ryy), I need the following conditional formatting conditions created: 1 - Shade row grey if column "K" value = "Completed" 2 - Shade row grey if column "K" value = "Cancelled" In addition, column "I" needs to have a 3rd condition: 3 - Make column "I" value red font if condition met (formula =$Jx < 3) I recorded a macro for creating the conditions & got the following code that I can copy to cells. Just not sure how to generalize it so it will not reference cell $K701 below (it needs to reference columns K & J but apply to all rows in the range): Range(strI_Range).Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=$K701 = ""Completed""" Selection.FormatConditions(1).Interior.ColorIndex = 33 Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=$K701 = ""Cancelled""" Selection.FormatConditions(2).Interior.ColorIndex = 33 ' 3rd condition in cond fmtg sub for column I only Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$J701 < 3" With Selection.FormatConditions(3).Font .Bold = True .Italic = False .ColorIndex = 3 End With If there is a more elegant way to do this, please let me know. Right now I have this as 2 subs (one for col I & the other for the other cells in the range). I also have a Click Event in Sheet 1 that shades a column for different values. Not sure how to combine them (if that's possible and/or if it is worth doing). Thanks for your help! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting Code
Thanks for the code, Bob ...
I tried it & it generates a "Compile Error - Syntax Error" on the following lines of code: ..FormatConditions.Add Type:=xlExpression, _ Formula1:="=$K" & ActiveCell.Row & All of the conditions are appearing red in the VBA editor. Thanks for you help with this. -- JoAnn "Bob Phillips" wrote: With Range(strI_Range).EntireRow .FormatConditions.Delete .FormatConditions.Add Type:=xlExpression, _ Formula1:="=$K" & ActiveCell.Row & "=""Completed""" .FormatConditions(1).Interior.ColorIndex = 33 .FormatConditions.Add Type:=xlExpression, _ Formula1:="=$K" & ActiveCell.Row & "=""Cancelled""" .FormatConditions(2).Interior.ColorIndex = 33 .FormatConditions.Add Type:=xlExpression, _ Formula1:="=AND(COLUMN()=9,$J" & ActiveCell.Row & "<=3)" With .FormatConditions(3).Font .Bold = True .Italic = False .ColorIndex = 3 End With End With -- __________________________________ HTH Bob "JoAnn" wrote in message ... Need help creating the code for adding conditional formatting to cell ranges . For range (Axx:Ryy), I need the following conditional formatting conditions created: 1 - Shade row grey if column "K" value = "Completed" 2 - Shade row grey if column "K" value = "Cancelled" In addition, column "I" needs to have a 3rd condition: 3 - Make column "I" value red font if condition met (formula =$Jx < 3) I recorded a macro for creating the conditions & got the following code that I can copy to cells. Just not sure how to generalize it so it will not reference cell $K701 below (it needs to reference columns K & J but apply to all rows in the range): Range(strI_Range).Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=$K701 = ""Completed""" Selection.FormatConditions(1).Interior.ColorIndex = 33 Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=$K701 = ""Cancelled""" Selection.FormatConditions(2).Interior.ColorIndex = 33 ' 3rd condition in cond fmtg sub for column I only Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$J701 < 3" With Selection.FormatConditions(3).Font .Bold = True .Italic = False .ColorIndex = 3 End With If there is a more elegant way to do this, please let me know. Right now I have this as 2 subs (one for col I & the other for the other cells in the range). I also have a Click Event in Sheet 1 that shades a column for different values. Not sure how to combine them (if that's possible and/or if it is worth doing). Thanks for your help! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting Code
It was NG wrap-around. Try this version
With Range(strI_Range).EntireRow .FormatConditions.Delete .FormatConditions.Add Type:=xlExpression, _ Formula1:="=$K" & ActiveCell.Row & "=""Completed""" .FormatConditions(1).Interior.ColorIndex = 33 .FormatConditions.Add Type:=xlExpression, _ Formula1:="=$K" & ActiveCell.Row & "=""Cancelled""" .FormatConditions(2).Interior.ColorIndex = 33 .FormatConditions.Add Type:=xlExpression, _ Formula1:="=AND(COLUMN()=9,$J" & ActiveCell.Row & "<=3)" With .FormatConditions(3).Font .Bold = True .Italic = False .ColorIndex = 3 End With End With -- __________________________________ HTH Bob "JoAnn" wrote in message ... Thanks for the code, Bob ... I tried it & it generates a "Compile Error - Syntax Error" on the following lines of code: .FormatConditions.Add Type:=xlExpression, _ Formula1:="=$K" & ActiveCell.Row & All of the conditions are appearing red in the VBA editor. Thanks for you help with this. -- JoAnn "Bob Phillips" wrote: With Range(strI_Range).EntireRow .FormatConditions.Delete .FormatConditions.Add Type:=xlExpression, _ Formula1:="=$K" & ActiveCell.Row & "=""Completed""" .FormatConditions(1).Interior.ColorIndex = 33 .FormatConditions.Add Type:=xlExpression, _ Formula1:="=$K" & ActiveCell.Row & "=""Cancelled""" .FormatConditions(2).Interior.ColorIndex = 33 .FormatConditions.Add Type:=xlExpression, _ Formula1:="=AND(COLUMN()=9,$J" & ActiveCell.Row & "<=3)" With .FormatConditions(3).Font .Bold = True .Italic = False .ColorIndex = 3 End With End With -- __________________________________ HTH Bob "JoAnn" wrote in message ... Need help creating the code for adding conditional formatting to cell ranges . For range (Axx:Ryy), I need the following conditional formatting conditions created: 1 - Shade row grey if column "K" value = "Completed" 2 - Shade row grey if column "K" value = "Cancelled" In addition, column "I" needs to have a 3rd condition: 3 - Make column "I" value red font if condition met (formula =$Jx < 3) I recorded a macro for creating the conditions & got the following code that I can copy to cells. Just not sure how to generalize it so it will not reference cell $K701 below (it needs to reference columns K & J but apply to all rows in the range): Range(strI_Range).Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=$K701 = ""Completed""" Selection.FormatConditions(1).Interior.ColorIndex = 33 Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=$K701 = ""Cancelled""" Selection.FormatConditions(2).Interior.ColorIndex = 33 ' 3rd condition in cond fmtg sub for column I only Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$J701 < 3" With Selection.FormatConditions(3).Font .Bold = True .Italic = False .ColorIndex = 3 End With If there is a more elegant way to do this, please let me know. Right now I have this as 2 subs (one for col I & the other for the other cells in the range). I also have a Click Event in Sheet 1 that shades a column for different values. Not sure how to combine them (if that's possible and/or if it is worth doing). Thanks for your help! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting Code
Thanks! This works great.
-- JoAnn "Bob Phillips" wrote: It was NG wrap-around. Try this version With Range(strI_Range).EntireRow .FormatConditions.Delete .FormatConditions.Add Type:=xlExpression, _ Formula1:="=$K" & ActiveCell.Row & "=""Completed""" .FormatConditions(1).Interior.ColorIndex = 33 .FormatConditions.Add Type:=xlExpression, _ Formula1:="=$K" & ActiveCell.Row & "=""Cancelled""" .FormatConditions(2).Interior.ColorIndex = 33 .FormatConditions.Add Type:=xlExpression, _ Formula1:="=AND(COLUMN()=9,$J" & ActiveCell.Row & "<=3)" With .FormatConditions(3).Font .Bold = True .Italic = False .ColorIndex = 3 End With End With -- __________________________________ HTH Bob "JoAnn" wrote in message ... Thanks for the code, Bob ... I tried it & it generates a "Compile Error - Syntax Error" on the following lines of code: .FormatConditions.Add Type:=xlExpression, _ Formula1:="=$K" & ActiveCell.Row & All of the conditions are appearing red in the VBA editor. Thanks for you help with this. -- JoAnn "Bob Phillips" wrote: With Range(strI_Range).EntireRow .FormatConditions.Delete .FormatConditions.Add Type:=xlExpression, _ Formula1:="=$K" & ActiveCell.Row & "=""Completed""" .FormatConditions(1).Interior.ColorIndex = 33 .FormatConditions.Add Type:=xlExpression, _ Formula1:="=$K" & ActiveCell.Row & "=""Cancelled""" .FormatConditions(2).Interior.ColorIndex = 33 .FormatConditions.Add Type:=xlExpression, _ Formula1:="=AND(COLUMN()=9,$J" & ActiveCell.Row & "<=3)" With .FormatConditions(3).Font .Bold = True .Italic = False .ColorIndex = 3 End With End With -- __________________________________ HTH Bob "JoAnn" wrote in message ... Need help creating the code for adding conditional formatting to cell ranges . For range (Axx:Ryy), I need the following conditional formatting conditions created: 1 - Shade row grey if column "K" value = "Completed" 2 - Shade row grey if column "K" value = "Cancelled" In addition, column "I" needs to have a 3rd condition: 3 - Make column "I" value red font if condition met (formula =$Jx < 3) I recorded a macro for creating the conditions & got the following code that I can copy to cells. Just not sure how to generalize it so it will not reference cell $K701 below (it needs to reference columns K & J but apply to all rows in the range): Range(strI_Range).Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=$K701 = ""Completed""" Selection.FormatConditions(1).Interior.ColorIndex = 33 Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=$K701 = ""Cancelled""" Selection.FormatConditions(2).Interior.ColorIndex = 33 ' 3rd condition in cond fmtg sub for column I only Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$J701 < 3" With Selection.FormatConditions(3).Font .Bold = True .Italic = False .ColorIndex = 3 End With If there is a more elegant way to do this, please let me know. Right now I have this as 2 subs (one for col I & the other for the other cells in the range). I also have a Click Event in Sheet 1 that shades a column for different values. Not sure how to combine them (if that's possible and/or if it is worth doing). Thanks for your help! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formatting vb code | Excel Programming | |||
Conditional Formatting using code | Excel Discussion (Misc queries) | |||
Conditional Formatting to VBA code | Excel Programming | |||
Need help to changes a conditional formatting code.... | Excel Programming | |||
Conditional Formatting using VBA Code | Excel Programming |