Mr Boris
New Member
- Joined
- Feb 25, 2015
- Messages
- 10
Hi Excel VBA Gurus!
H.E.L.P. P.L.E.A.S.E
Am new here and hope someone can help, I am literally pulling my hair out.
I have searched and searched and not come up with a solution and cannot explain what is happening.
Basically I have a worksheet in Excel 2013 (on a Win 7 X86 machine) that has a worksheet with a selection of invoicing data on each row and many new rows are added each month. The worksheet has 8 conditional formats on it at various ranges which extend from the Top of the invoicing data to the bottom of the data .
As new rows of data get inserted [at the top of the range] in to the range using this approach:
<!-- end snippet -->the conditional formatting gets convoluted, messy, fragmented, duplicated for the specific new row and eventually after a few months is such a mess it seems stops working correctly.
After wasting several hours on various approaches my solution was to be "simple". (SURE!) When a row gets inserted at the top of the range then I want to clear and reset the 8 x conditional formats using VBA (- in essence perform a quick clean-up) by deleting all of the the conditional formatting on the worksheet and reapplying 8 clean conditional formats with the correct ranges and formatting for each of the ranges using the known Top and Bottom of the data range variables. With me so far?
The problem is that after adding and formatting 4 conditional formats using .FormatConditions.Add and .FormatConditions(x), Excel throws a Subscript Error when I attempt to format the CF Item 5 (or more) Font or background color. I have searched for an explanation and understand that basically I can add as many CF's as I like so what is going on?
The crazy thing is that it appeared to work correctly once then never again?
Here is my test code and it all works perfectly until formula 5, where it crashes. I have attempted to work around it but any CF index greater than 4 using .FormatConditions(x) crashes it.
<!-- end snippet -->
If I rem out the formatting of font and background the CF rule is added to the worksheet with formula and correct range but clearly without formatting this is a waste of time !
I also attempted a different way of formatting the font color and Background color however selecting a Conditional Formatting item with an index higher than 4 returns the same error.
<!-- end snippet -->
I have included my remarks which are my notes for each of the formats - these are actually from my original CF's.
**Any assistance - guidance etc would be gratefully received. Feeling desperate**!
Seriously, I have wasted much time on this and cannot understand what the issue is. Why wont it let me add and format more than 4 items without an error and can I SOME HOW work around this problem.
Many thanks and kind regards in advance for any responses.
Mr Boris</today(),d"></today(),d17<>
H.E.L.P. P.L.E.A.S.E
Am new here and hope someone can help, I am literally pulling my hair out.
I have searched and searched and not come up with a solution and cannot explain what is happening.
Basically I have a worksheet in Excel 2013 (on a Win 7 X86 machine) that has a worksheet with a selection of invoicing data on each row and many new rows are added each month. The worksheet has 8 conditional formats on it at various ranges which extend from the Top of the invoicing data to the bottom of the data .
As new rows of data get inserted [at the top of the range] in to the range using this approach:
Code:
Manager.Select
Manager.Range("headerRow").Offset(1, 0).Range("a1:M1").Select
Selection.Copy
Selection.Insert Shift:=xlDown<!-- end snippet -->the conditional formatting gets convoluted, messy, fragmented, duplicated for the specific new row and eventually after a few months is such a mess it seems stops working correctly.
After wasting several hours on various approaches my solution was to be "simple". (SURE!) When a row gets inserted at the top of the range then I want to clear and reset the 8 x conditional formats using VBA (- in essence perform a quick clean-up) by deleting all of the the conditional formatting on the worksheet and reapplying 8 clean conditional formats with the correct ranges and formatting for each of the ranges using the known Top and Bottom of the data range variables. With me so far?
The problem is that after adding and formatting 4 conditional formats using .FormatConditions.Add and .FormatConditions(x), Excel throws a Subscript Error when I attempt to format the CF Item 5 (or more) Font or background color. I have searched for an explanation and understand that basically I can add as many CF's as I like so what is going on?
The crazy thing is that it appeared to work correctly once then never again?
Here is my test code and it all works perfectly until formula 5, where it crashes. I have attempted to work around it but any CF index greater than 4 using .FormatConditions(x) crashes it.
Code:
Sub CFReset()
' Get top and bottom of Invoices
TopRow = (Manager.Range("headerRow").Row) + 1
BottomRow = ActiveSheet.UsedRange.Rows.Count
' Clear All Current Conditional Formatting on Manager Sheet between Invoice Top Row and Invoice Bottom Row
Range("A" & TopRow & ":L" & BottomRow).FormatConditions.Delete
'CD Formula 1 - 'Formula: =AND(I17="Paid",G17<>0) - 'Colour: Red on White Background 'Applies to: =$G$17:$G$49
With ActiveSheet.Range("$G$" & TopRow & ":$G$" & BottomRow)
.FormatConditions.Add Type:=xlExpression, Formula1:="=AND(I" & TopRow & "=""Paid"",G" & TopRow & "<>0)"
.FormatConditions(1).Font.ColorIndex = 3 ' Red
End With
'CD Formula 2 - '=AND(I17="Partial",F17<>0) 'Colour: Red on white background 'Applies to: =$G$17:$G$49
With ActiveSheet.Range("$G$" & TopRow & ":$G$" & BottomRow)
.FormatConditions.Add Type:=xlExpression, Formula1:="=AND(I" & TopRow & "=""Partial"",F" & TopRow & "<>0)"
.FormatConditions(2).Font.ColorIndex = 3 ' Red
End With
'CD Formula 3 '=OR($I17="Void") - Text Colour RGB: 255,179,179 'Applies to: =$A$17:$I$49
With ActiveSheet.Range("$A$" & TopRow & ":$I$" & BottomRow)
.FormatConditions.Add Type:=xlExpression, Formula1:="=OR($I" & TopRow & "=""Void"")"
.FormatConditions(3).Font.Color = RGB(255, 179, 179) ' Pinkish
End With
'CD Formula 4 - '=OR($I17="Paid",$I17="Closed") - Text Colour: RGB 192, 192, 192 'Applies to: =$A$17:$I$49
With ActiveSheet.Range("$A$" & TopRow & ":$I$" & BottomRow)
.FormatConditions.Add Type:=xlExpression, Formula1:="=OR($I" & TopRow & "=""Paid"",$I" & TopRow & "=""Closed"")"
.FormatConditions(4).Font.Color = RGB(192, 192, 192) ' Gray
End With
'CD Formula 5 '=AND(I17="Paid",G17<>0) 'Color: RGB 0, 0, 0 'Black 'Background Colour:RGB: 255,255,204 ' Light Yellow 'Applies to: =$F$17:$F$49
With ActiveSheet.Range("$F$" & TopRow & ":$F$" & BottomRow)
.FormatConditions.Add Type:=xlExpression, Formula1:="=AND(I" & TopRow & "=""Paid"",G" & TopRow & "<>0)"
.FormatConditions(5).Font.ColorIndex = 1 ' Black
.FormatConditions(5).Interior.Color = RGB(255, 255, 204) ' Light Yellow
End With
'CD Formula 6 '=AND(I17="Partial",F17=0) 'Color: RGB 0, 0, 0 'Black 'Background Colour:RGB: 255,255,204 ' Light Yellow 'Applies to: =$F$17:$F$49
With ActiveSheet.Range("$F$" & TopRow & ":$F$" & BottomRow)
.FormatConditions.Add Type:=xlExpression, Formula1:="=AND(I" & TopRow & "=""Partial"",F" & TopRow & "=0)"
.FormatConditions(6).Font.ColorIndex = 1 ' Black
.FormatConditions(6).Interior.Color = RGB(255, 255, 204) ' Light Yellow
End With
'CD Formula 7 '=AND(G17>0,AND(D17<today(),d17<>"")) 'Colour: Red 'Background Colour:RGB: 255,255,204 ' Light Yellow 'Applies to: =$D$17:$D$49
With ActiveSheet.Range("$D$" & TopRow & ":$D$" & BottomRow)
.FormatConditions.Add Type:=xlExpression, Formula1:="=AND(G" & TopRow & ">0,AND(D" & TopRow & "<today(),d" &="" toprow="" "<="">""""))"
.FormatConditions(7).Font.ColorIndex = 3 ' Red
.FormatConditions(7).Interior.Color = RGB(255, 255, 204) ' Light Yellow
End With
'CD Formula 8 '=COUNTIF($B:$B,B17)>1 'Color: RGB 255,255,255 -'White 'Background Colour: Red ' Rgb 255 ' Applies to: =$B$17:$B$49
With ActiveSheet.Range("$B$" & TopRow & ":$B$" & BottomRow)
.FormatConditions.Add Type:=xlExpression, Formula1:="=COUNTIF($B:$B,B" & TopRow & ")>1"
.FormatConditions(8).Font.ColorIndex = 2 ' White
.FormatConditions(8).Interior.ColorIndex = 3 ' Red
End With
End SubIf I rem out the formatting of font and background the CF rule is added to the worksheet with formula and correct range but clearly without formatting this is a waste of time !
I also attempted a different way of formatting the font color and Background color however selecting a Conditional Formatting item with an index higher than 4 returns the same error.
Code:
'Attempt2
With ActiveSheet.Range("$A$" & TopRow & ":$I$" & BottomRow).FormatConditions
.Add Type:=xlExpression, Formula1:="=OR($I" & TopRow & "=""Void"")"
With .Item(3).Font
.Color = RGB(255, 179, 179) ' Pinkish
End With
End WithI have included my remarks which are my notes for each of the formats - these are actually from my original CF's.
**Any assistance - guidance etc would be gratefully received. Feeling desperate**!
Seriously, I have wasted much time on this and cannot understand what the issue is. Why wont it let me add and format more than 4 items without an error and can I SOME HOW work around this problem.
Many thanks and kind regards in advance for any responses.
Mr Boris</today(),d"></today(),d17<>
Last edited:
