ExcelBanter - Conditional Formatting Code

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Conditional Formatting Code (https://www.excelbanter.com/excel-programming/412842-conditional-formatting-code.html)

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!

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!

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!

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!

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!