Never Looped before
 

I have a range of about 6000 rows, 33 columns (starting at column D going to
column AJ).

The issue. Starting at row 5 I want to place a "loop" to conditionally
format row 6 based on the value in row 5. I know that the conditional format
would look like this:

Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=D6<D5"
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = True
.ColorIndex = 2
End With
Selection.FormatConditions(1).Interior.ColorIndex = 3
Selection.AutoFill Destination:=Range("D6:AJ6"), Type:=xlFillFormats
Range("D6:AJ6").Select

but I need to repeat this for every row until it reaches the end of the
pivot table. I was copying and pasting fomats but excel didnt like that for
some reason.

I'm hoping an automatic code can be written into this sheet so that it does
it automattically...that would be helpful.

Thank you in advance and please let me know if you need ANY further
information.

Never Looped before
 

You do not need to loop here, either.

Sub TryNow()
With Range("D6", Cells(Rows.Count, 4).End(xlUp))
.Select
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=D6<D5"
With .FormatConditions(1).Font
.Bold = True
.Italic = True
.ColorIndex = 2
End With
.FormatConditions(1).Interior.ColorIndex = 3
End With
End Sub

HTH,
Bernie
MS Excel MVP


"ahern79" wrote in message
...
I have a range of about 6000 rows, 33 columns (starting at column D going to
column AJ).

The issue. Starting at row 5 I want to place a "loop" to conditionally
format row 6 based on the value in row 5. I know that the conditional format
would look like this:

Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=D6<D5"
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = True
.ColorIndex = 2
End With
Selection.FormatConditions(1).Interior.ColorIndex = 3
Selection.AutoFill Destination:=Range("D6:AJ6"), Type:=xlFillFormats
Range("D6:AJ6").Select

but I need to repeat this for every row until it reaches the end of the
pivot table. I was copying and pasting fomats but excel didnt like that for
some reason.

I'm hoping an automatic code can be written into this sheet so that it does
it automattically...that would be helpful.

Thank you in advance and please let me know if you need ANY further
information.

Never Looped before
 

Bernie,

Will this go through every other row (6, 8, 10, 12...) till it reaches the
end of the range?

"Bernie Deitrick" wrote:

You do not need to loop here, either.

Sub TryNow()
With Range("D6", Cells(Rows.Count, 4).End(xlUp))
.Select
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=D6<D5"
With .FormatConditions(1).Font
.Bold = True
.Italic = True
.ColorIndex = 2
End With
.FormatConditions(1).Interior.ColorIndex = 3
End With
End Sub

HTH,
Bernie
MS Excel MVP


"ahern79" wrote in message
...
I have a range of about 6000 rows, 33 columns (starting at column D going to
column AJ).

The issue. Starting at row 5 I want to place a "loop" to conditionally
format row 6 based on the value in row 5. I know that the conditional format
would look like this:

Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=D6<D5"
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = True
.ColorIndex = 2
End With
Selection.FormatConditions(1).Interior.ColorIndex = 3
Selection.AutoFill Destination:=Range("D6:AJ6"), Type:=xlFillFormats
Range("D6:AJ6").Select

but I need to repeat this for every row until it reaches the end of the
pivot table. I was copying and pasting fomats but excel didnt like that for
some reason.

I'm hoping an automatic code can be written into this sheet so that it does
it automattically...that would be helpful.

Thank you in advance and please let me know if you need ANY further
information.

Never Looped before
 

Bernie,

I just tried that code, and I thank you for the help, however it didnt work
correctly.

Perhaps I wasnt clear in my needs, let me try again.

I have a range from D5 going to AJ6658

The structure is acutally a Pivot table showing the Average and Max Values
for some 3300+ Facilities. I need to hilight whenever the Max Value is
different from the Average Value. that is why the conditional format shows
D6<D5. I need to actually say something like:

"Max of Value" < "Average of Value"
for columns D:AJ
and repeat till end of pivottable.

Sorry for the confusion, and I still hope someone can help.
Thanks

"Bernie Deitrick" wrote:

You do not need to loop here, either.

Sub TryNow()
With Range("D6", Cells(Rows.Count, 4).End(xlUp))
.Select
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=D6<D5"
With .FormatConditions(1).Font
.Bold = True
.Italic = True
.ColorIndex = 2
End With
.FormatConditions(1).Interior.ColorIndex = 3
End With
End Sub

HTH,
Bernie
MS Excel MVP


"ahern79" wrote in message
...
I have a range of about 6000 rows, 33 columns (starting at column D going to
column AJ).

The issue. Starting at row 5 I want to place a "loop" to conditionally
format row 6 based on the value in row 5. I know that the conditional format
would look like this:

Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=D6<D5"
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = True
.ColorIndex = 2
End With
Selection.FormatConditions(1).Interior.ColorIndex = 3
Selection.AutoFill Destination:=Range("D6:AJ6"), Type:=xlFillFormats
Range("D6:AJ6").Select

but I need to repeat this for every row until it reaches the end of the
pivot table. I was copying and pasting fomats but excel didnt like that for
some reason.

I'm hoping an automatic code can be written into this sheet so that it does
it automattically...that would be helpful.

Thank you in advance and please let me know if you need ANY further
information.

Never Looped before
 

Well, that is a whole other kettle of fish... try this instead:

Sub TryNowV2()
With Range("D5", Range("AJ" & Rows.Count).End(xlUp))
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=OR(AND(MOD(ROW(D5),2)=0,D5<D4),AND(MOD(ROW(D5), 2)=1,D5<D6))"
With .FormatConditions(1).Font
.Bold = True
.Italic = True
.ColorIndex = 2
End With
.FormatConditions(1).Interior.ColorIndex = 3
End With
End Sub

HTH,
Bernie
MS Excel MVP


"ahern79" wrote in message
...
Bernie,

I just tried that code, and I thank you for the help, however it didnt work
correctly.

Perhaps I wasnt clear in my needs, let me try again.

I have a range from D5 going to AJ6658

The structure is acutally a Pivot table showing the Average and Max Values
for some 3300+ Facilities. I need to hilight whenever the Max Value is
different from the Average Value. that is why the conditional format shows
D6<D5. I need to actually say something like:

"Max of Value" < "Average of Value"
for columns D:AJ
and repeat till end of pivottable.

Sorry for the confusion, and I still hope someone can help.
Thanks

"Bernie Deitrick" wrote:

You do not need to loop here, either.

Sub TryNow()
With Range("D6", Cells(Rows.Count, 4).End(xlUp))
.Select
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=D6<D5"
With .FormatConditions(1).Font
.Bold = True
.Italic = True
.ColorIndex = 2
End With
.FormatConditions(1).Interior.ColorIndex = 3
End With
End Sub

HTH,
Bernie
MS Excel MVP


"ahern79" wrote in message
...
I have a range of about 6000 rows, 33 columns (starting at column D going to
column AJ).

The issue. Starting at row 5 I want to place a "loop" to conditionally
format row 6 based on the value in row 5. I know that the conditional format
would look like this:

Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=D6<D5"
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = True
.ColorIndex = 2
End With
Selection.FormatConditions(1).Interior.ColorIndex = 3
Selection.AutoFill Destination:=Range("D6:AJ6"), Type:=xlFillFormats
Range("D6:AJ6").Select

but I need to repeat this for every row until it reaches the end of the
pivot table. I was copying and pasting fomats but excel didnt like that for
some reason.

I'm hoping an automatic code can be written into this sheet so that it does
it automattically...that would be helpful.

Thank you in advance and please let me know if you need ANY further
information.

Never Looped before
 

Thank you,

As long as I remember to apply the marco with D5 as active it works well
enough for me to deal with.

Thanks a mill.


"Bernie Deitrick" wrote:

Well, that is a whole other kettle of fish... try this instead:

Sub TryNowV2()
With Range("D5", Range("AJ" & Rows.Count).End(xlUp))
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=OR(AND(MOD(ROW(D5),2)=0,D5<D4),AND(MOD(ROW(D5), 2)=1,D5<D6))"
With .FormatConditions(1).Font
.Bold = True
.Italic = True
.ColorIndex = 2
End With
.FormatConditions(1).Interior.ColorIndex = 3
End With
End Sub

HTH,
Bernie
MS Excel MVP


"ahern79" wrote in message
...
Bernie,

I just tried that code, and I thank you for the help, however it didnt work
correctly.

Perhaps I wasnt clear in my needs, let me try again.

I have a range from D5 going to AJ6658

The structure is acutally a Pivot table showing the Average and Max Values
for some 3300+ Facilities. I need to hilight whenever the Max Value is
different from the Average Value. that is why the conditional format shows
D6<D5. I need to actually say something like:

"Max of Value" < "Average of Value"
for columns D:AJ
and repeat till end of pivottable.

Sorry for the confusion, and I still hope someone can help.
Thanks

"Bernie Deitrick" wrote:

You do not need to loop here, either.

Sub TryNow()
With Range("D6", Cells(Rows.Count, 4).End(xlUp))
.Select
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=D6<D5"
With .FormatConditions(1).Font
.Bold = True
.Italic = True
.ColorIndex = 2
End With
.FormatConditions(1).Interior.ColorIndex = 3
End With
End Sub

HTH,
Bernie
MS Excel MVP


"ahern79" wrote in message
...
I have a range of about 6000 rows, 33 columns (starting at column D going to
column AJ).

The issue. Starting at row 5 I want to place a "loop" to conditionally
format row 6 based on the value in row 5. I know that the conditional format
would look like this:

Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=D6<D5"
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = True
.ColorIndex = 2
End With
Selection.FormatConditions(1).Interior.ColorIndex = 3
Selection.AutoFill Destination:=Range("D6:AJ6"), Type:=xlFillFormats
Range("D6:AJ6").Select

but I need to repeat this for every row until it reaches the end of the
pivot table. I was copying and pasting fomats but excel didnt like that for
some reason.

I'm hoping an automatic code can be written into this sheet so that it does
it automattically...that would be helpful.

Thank you in advance and please let me know if you need ANY further
information.

Never Looped before
 

No need to remember - just add a .Select line after the first With statement

With Range("D5", Range("AJ" & Rows.Count).End(xlUp))
.Select

HTH,
Bernie
MS Excel MVP


Thank you,

As long as I remember to apply the marco with D5 as active it works well
enough for me to deal with.

Thanks a mill.