thanks for the compiment
next post is rather long and will evoluate to something completely different then the code we have now
take your time to experiment and to understand
there was still an inconsistency (not so good for a legend
)
Code:
Do While row1 < 150
With Range("A" & row1 & ":I" & row1).FormatConditions
.Delete
.Add Type:=xlExpression, Formula1:="=$I$" & row1 & "=""Y"""
.Item(1).Interior.ColorIndex = 4
End With
row1 = row1 + 1
If row1 = 150 Then Exit Do
Loop
the line
Code:
If row1 = 150 Then Exit Do
was not needed
I didn't pay attention since I tried to explain how your code could be edited
BUT, back to work!
this code is not the way I would do it
instead of
Do ... Loop, which is used mostly when you don't know exactly where it will end, you would better use
For ... Next
the variable "i" will loop from "row1" to 149, just what you need
Code:
Option Explicit
Sub format()
'
' format Macro
' Macro recorded 13/10/2006 by me
'
Dim row1 As Long
Dim i As Long
On Error Resume Next
row1 = Range("P1")
On Error GoTo 0
row1 = Range("P1")
If row1 < 1 Or row1 > Rows.Count Then
MsgBox "error message", 48, "ERROR title"
Exit Sub
End If
For i = row1 To 149
With Range("A" & row1 & ":I" & row1).FormatConditions
.Delete
.Add Type:=xlExpression, Formula1:="=$I$" & row1 & "=""Y"""
.Item(1).Interior.ColorIndex = 4
End With
Next i
Range("P1") = row1
Range("A1").Select
End Sub
FINALLY all this was just meant to help you understand looping code
but as stated earlier: "do you really need a loop???"
when setting the range at once, you don't need the loop
Code:
With Range("A" & row1 & ":I149").FormatConditions
I didn't test next code, but you see the picture
Code:
Option Explicit
Sub format()
'
' format Macro
' Macro recorded 13/10/2006 by me
'
Dim row1 As Long
On Error Resume Next
row1 = Range("P1")
On Error GoTo 0
row1 = Range("P1")
If row1 < 1 Or row1 > 149 Then
MsgBox "error message", 48, "ERROR title"
Exit Sub
End If
With Range("A" & row1 & ":I149").FormatConditions
.Delete
.Add Type:=xlExpression, Formula1:="=$I$" & row1 & "=""Y"""
.Item(1).Interior.ColorIndex = 4
End With
Range("P1") = 149
Range("A1").Select
End Sub
if this is working for you...
will "149" be changed sometime or never ?