you need a "$" to "fix" the row for conditional format
.Add Type:=xlExpression, Formula1:="=$I$" & row1 & "=""Y"""
also the "switch" is not needed
in fact you don't even have a working switch, since there is a typo in your code
(you could have been noticed of this typo if you had "option explicit" on top of your code)
this is your code
with the "$"
quoted out some lines which are not necessary
indented another way for more clarity (perhaps that's personal)
Code:
Sub format()
'
' format Macro
' Macro recorded 13/10/2006 by me
'
'Dim switch
'switch = True
Dim row1 As Integer
row1 = Range("P1")
'Do
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
Range("P1") = row1
If row1 = 150 Then
'switch = False
Exit Do
End If
Loop
Range("A1").Select
'Loop Until siwtch = False
End Sub
next code
added "on error" in case P1 has no number
P1 must be a valid rownumber: added some code
added "Option Explicit"
deleted unneccesary lines
changed datatype of row1 to "Long"
you would not need all these error checks if you are really sure P1 can never be wrong datatype nor wrong rownumber
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 > Rows.Count Then
MsgBox "error message", 48, "ERROR title"
Exit Sub
End If
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
Range("P1") = row1
Range("A1").Select
End Sub
this was for learning purposes since you don't need to loop, unless I'mm missing something
if you want to change an entire range at once, you don't need to loop
if this is working for you I can help you with the non-loop-code
best regards,
Erik