Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Looped Find and Array Doesn't Find | Excel Programming | |||
Stopping looped "Find" command | Excel Discussion (Misc queries) | |||
Finding Maximum Within Range (Looped) | Excel Programming | |||
looped functions | Excel Discussion (Misc queries) | |||
Looped process | Excel Programming |